1:构造一个操作数据库的工具类,可以获得连接和释放连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
public class DBUtil { private static Connection conn = null ; static { //静态初始块 try { Class.forName( "com.mysql.jdbc.Driver" ); conn = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/test" , "root" , "064319" ); //初始化获取连接 } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } /** * 释放连接 * @param rs * @param psmt * @param conn * @throws SQLException */ public static void closeAll(ResultSet rs, PreparedStatement psmt, Connection conn) throws SQLException { if (rs != null ) { rs.close(); } if (psmt != null ) { psmt.close(); } if (conn != null ) { conn.close(); } } /** * 获取连接 * @return */ public static Connection getConnection() { return conn; } /** * 根据表的名字来获得表的列信息 * @param tableName */ public static void getTableColumnInfoByTableName(String tableName) { Connection conn = getConnection(); ResultSet rs = null ; PreparedStatement psmt = null ; String sql = "select * from " + tableName; try { psmt = conn.prepareStatement(sql); rs = psmt.executeQuery(); ResultSetMetaData resultSetMetaData = rs.getMetaData(); for ( int i = 1 ; i<= resultSetMetaData.getColumnCount(); i++) { System.out.println(resultSetMetaData.getColumnName(i)); } } catch (SQLException e) { e.printStackTrace(); } finally { try { closeAll(rs, psmt, conn); } catch (SQLException e) { e.printStackTrace(); } } } /** * 根据表的名字来获得表的信息 * @param tableName */ public static void getTableInfoByTableName(String tableName) { Connection conn = getConnection(); PreparedStatement psmt = null ; ResultSet rs = null ; String sql = "select * from " + tableName; try { psmt = conn.prepareStatement(sql); rs = psmt.executeQuery(); while (rs.next()) { ResultSetMetaData resultSetMetaData = rs.getMetaData(); for ( int i = 1 ; i<= resultSetMetaData.getColumnCount(); i++) { if (i < resultSetMetaData.getColumnCount()) { System.out.print(rs.getObject(resultSetMetaData.getColumnName(i)) + ", " ); } else { System.out.print(rs.getObject(resultSetMetaData.getColumnName(i))); } } System.out.println(); } } catch (SQLException e) { e.printStackTrace(); } finally { try { closeAll(rs, psmt, conn); } catch (SQLException e) { e.printStackTrace(); } } } } |
2:构造一个操作数据库的BaseDao类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
|
public class BaseDao { /** * 根据一些参数来保存相应的对象 * @param sql 要执行的sql语句 * @param params 为sql语句中相应的参数赋值 * @return */ protected boolean saveOrUpdate(String sql, Object[] params) { Connection conn = null ; PreparedStatement psmt = null ; boolean flag = false ; conn = DBUtil.getConnection(); if (conn != null ) { try { psmt = conn.prepareStatement(sql); for ( int i = 1 ; i <= params.length; i++) { psmt.setObject(i, params[i- 1 ]); } if (psmt.executeUpdate() > 0 ) { flag = true ; } } catch (SQLException e) { e.printStackTrace(); } finally { try { DBUtil.closeAll( null , psmt, conn); } catch (SQLException e) { e.printStackTrace(); } } } return flag; } /** * 根据一定的参数获得某个具体的对象 * @param sql 要执行的sql语句 * @param params 为sql语句中相应的参数赋值 * @return */ public Object queryForObject(String sql, Object[] params, RowMapForObject rowMapForObject) { Connection conn = null ; PreparedStatement psmt = null ; conn = DBUtil.getConnection(); Object obj = null ; ResultSet rs = null ; if (conn != null ) { try { psmt = conn.prepareStatement(sql); if (params != null && params.length > 0 ) { for ( int i = 1 ; i <= params.length; i++) { psmt.setObject(i, params[i - 1 ]); } } rs = psmt.executeQuery(); obj = rowMapForObject.rowMapForObject(rs); } catch (SQLException e) { e.printStackTrace(); } finally { try { DBUtil.closeAll( null , psmt, conn); } catch (SQLException e) { e.printStackTrace(); } } } return obj; } /** * 根据相应的参数获得查询的结果集 * @param sql * @param params * @return */ public List queryForList(String sql, Object[] params, RowMapForList rowMapForList) { Connection conn = null ; PreparedStatement psmt = null ; conn = DBUtil.getConnection(); List list = null ; ResultSet rs = null ; if (conn != null ) { try { psmt = conn.prepareStatement(sql); if (params != null && params.length > 0 ) { for ( int i = 1 ; i <= params.length; i++) { psmt.setObject(i, params[i - 1 ]); } } rs = psmt.executeQuery(sql); list = new ArrayList(); list = rowMapForList.rowMapForList(rs); } catch (SQLException e) { e.printStackTrace(); } finally { try { DBUtil.closeAll( null , psmt, conn); } catch (SQLException e) { e.printStackTrace(); } } } return list; } } |
3:新建一个StudentDao类,该类继承自BaseDao,实现对Student的管理
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
public class StudentDao extends BaseDao { /** * 保存一个Student的信息 */ public boolean saveStudent(Student student) { String sql = "insert into t_student(name, age) values(?, ?)" ; Object[] params = new Object[]{student.getName(), student.getAge()}; return super .saveOrUpdate(sql, params); } /** * 根据id获得一个Student的信息 * @param id * @return */ public Student getStudentById( long id) { String sql = "select * from t_student where id=?" ; Object[] params = new Object[]{id}; return (Student) super .queryForObject(sql, params, new RowMapForObject() { public Object rowMapForObject(ResultSet rs) { Student student = null ; try { if (rs != null && rs.next()) { student = new Student(); student.setAge(rs.getInt(Student.AGE)); student.setId(rs.getLong(Student.ID)); student.setName(rs.getString(Student.NAME)); } } catch (SQLException e) { e.printStackTrace(); } return student; } }); } /** * 获得所有Student的信息 * @return */ public List getStudentAll() { String sql = "select * from t_student" ; List list = super .queryForList(sql, null , new RowMapForList() { @Override public List rowMapForList(ResultSet rs) { List list = null ; try { if (rs != null ) { list = new ArrayList(); while (rs.next()) { Student student = new Student(); student.setId(rs.getLong( "id" )); student.setAge(rs.getInt( "age" )); student.setName(rs.getString( "name" )); list.add(student); } } } catch (SQLException e) { e.printStackTrace(); } return list; } }); return list; } } |
如有疑问请留言或者到本站社区交流讨论,大家共同进步,感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
原文链接:http://blog.csdn.net/woshisap/article/details/7192822