1、JDBCUTILS
package com.helper.Utlis;import java.lang.reflect.Field;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;public class JdbcUtils { // 数据库的用户名 private final String USERNAME = "root"; // 数据库的密码 private final String PASSWORD = "fclbri"; // 数据库的驱动信息,记住mysql-connector-java.jar-->Build PAth private final String DRIVER = "com.mysql.jdbc.Driver"; // 访问数据库的地址，为什么一定是3306？？ private final String URL = "jdbc:mysql://localhost:3306/ClassHelper?seUnicode=true&characterEncoding=UTF-8"; // 数据库的连接 private Connection connection; // 执行sql语句的对象 private PreparedStatement pstmt; // 查询返回的数据集 private ResultSet resultSet; // private Statement stmt; public JdbcUtils() { // 加载JDBC驱动程序,创建JdbcUtils对象时会执行 try { Class.forName(DRIVER);// 加载 System.out.println("数据库驱动加载成功!"); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } } // 建立数据库连接 public Connection getConnection() { try { connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); System.out.println("数据库连接成功！"); } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } return connection; } public boolean deleteByBatch(String[] sql) throws SQLException { boolean flag = false; stmt = connection.createStatement(); if (sql != null) { for (int i = 0; i < sql.length; i++) { stmt.addBatch(sql[i]); } } int[] count = stmt.executeBatch(); if (count != null) { flag = true; } return flag; } /** * PrepareStatement用于执行动态的SQL语句 对表进行操作：添加，修改，删除 * * @param sql * @param params * @return * @throws SQLException */ public boolean updateByPreparedStatement(String sql, List params) throws SQLException { boolean flag = false; int result = -1;// 表示执行sql语句时影响数据库的行数 pstmt = connection.prepareStatement(sql); int index = 1; // 判断集合不为空的标准条件语句 if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } result = pstmt.executeUpdate();// 执行sql语句+返回执行后影响数据库的行数 flag = result > 0 ? true : false; return flag; } /** * 查询并返回单条记录 */ public Map
findSimpleResult(String sql, List
params) throws SQLException { Map
map = new HashMap
(); int index = 1; pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery();// 查询结果 ResultSetMetaData metaData = resultSet.getMetaData(); int col_len = metaData.getColumnCount();// 获得列的数量 while (resultSet.next()) // 如果有下一条记录 { for (int i = 0; i < col_len; i++) { String cols_name = metaData.getColumnName(i + 1);// 获得列的名称 Object cols_value = resultSet.getObject(cols_name); if (cols_value == null) { cols_value = ""; } map.put(cols_name, cols_value); } } return map; } /** * 查询并返回多条记录 * * @param sql * @param params * @return * @throws SQLException */ public List
> findMoreResult(String sql, List
params) throws SQLException { List
> list = new ArrayList
>(); int index = 1; pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while (resultSet.next()) { Map
map = new HashMap
(); for (int i = 0; i < cols_len; i++) { String cols_name = metaData.getColumnName(i + 1);// 获得列的名称 Object cols_value = resultSet.getObject(cols_name); if (cols_value == null) { cols_value = ""; } map.put(cols_name, cols_value); } list.add(map); } return list; } /** * JDBC的封装可以使用反射机制来封装 * * @param sql * @param params * @param cls * T表示泛型,这个类必须与查询的结果的所有列的字段一一对应，不多不少，类型相同 * @return * @throws Exception */ public
T findSimpleRefResult(String sql, List
params, Class
cls) throws Exception { T resultObject = null; int index = 1; pstmt = connection.prepareStatement(sql); // params数组依次给？赋值 if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery();// 查询的结果 ResultSetMetaData metaData = resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while (resultSet.next()) { // ͨ通过反射机制创建一个实例 resultObject = cls.newInstance(); for (int i = 0; i < cols_len; i++) { String cols_name = metaData.getColumnName(i + 1); Object cols_value = resultSet.getObject(cols_name); if (cols_value == null) { cols_value = ""; } Field field = cls.getDeclaredField(cols_name); field.setAccessible(true);// 打开javabean的访问private 权限 field.set(resultObject, cols_value); } } return resultObject; } /** * 返回多条记录 JDBC的封装可以使用反射机制来封装 T表示泛型 */ public
List
findMoreRefResult(String sql, List
params, Class
cls) throws Exception { List
list = new ArrayList
(); int index = 1; pstmt = connection.prepareStatement(sql); if (params != null && !params.isEmpty()) { for (int i = 0; i < params.size(); i++) { pstmt.setObject(index++, params.get(i)); } } resultSet = pstmt.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int cols_len = metaData.getColumnCount(); while (resultSet.next()) { T resultObject = cls.newInstance(); for (int i = 0; i < cols_len; i++) { String cols_name = metaData.getColumnName(i + 1); Object cols_value = resultSet.getObject(cols_name); if (cols_value == null) { cols_value = ""; } Field field = cls.getDeclaredField(cols_name); field.setAccessible(true); field.set(resultObject, cols_value); } list.add(resultObject); } return list; } // 关闭数据库的连接 public void releaseConn() { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }}
2、增删查改
增加
@Override public boolean addNotice(Notice notice) { String sql = "insert into tb_notice (title,body,exigency,stuid, timestamp) values (?,?,?,?,?)"; List params = new ArrayList(); params.add(notice.getTitle()); params.add(notice.getBody()); params.add(notice.isExigency()); params.add(notice.getStuid()); Timestamp ts = new Timestamp(System.currentTimeMillis()); params.add(ts.toString()); boolean flag = false; jdbcUtils.getConnection(); try { flag = jdbcUtils.updateByPreparedStatement(sql, params); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return flag; }
删除
@Override public boolean delNotice(String id) { // TODO Auto-generated method stub jdbcUtils.getConnection(); boolean flag=false; String[] sql={"delete from tb_notice where id='" + id + "'"}; try { flag=jdbcUtils.deleteByBatch(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return flag; }
查找
public List
listById(String openid) { // TODO Auto-generated method stub String sql = "select id,tb_daka.openid,tb_userinfo.nickname,time,content from tb_daka,tb_userinfo where tb_daka.openid=tb_userinfo.openid and tb_daka.openid=? order by time desc"; jdbcUtils.getConnection(); List
list = null; List
params=new ArrayList(); params.add(openid); try { list = jdbcUtils.findMoreRefResult(sql, params, DkRecord.class); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return list; }
更改
@Override public boolean editqq(String stuid, String qq) { List params=new ArrayList(); params.add(qq); params.add(stuid); boolean flag = false; jdbcUtils.getConnection(); String sql="update tb_student set qq=? where stuid=?"; try { flag=jdbcUtils.updateByPreparedStatement(sql, params); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return flag; }
Done！