服务端使用Druid连接池进行数据库连接，主要分为两步：
1.配置jdbc.properties文件
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/qqzone?useUnicode=true&characterEncoding=utf-8 username=root password=root
2.实现数据库的连接与关闭
【ConnectionUtil.java】
package com.javaweb.qqzone.myssm.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionUtil {
private static ThreadLocal threadLocal = new ThreadLocal<>();
public static Connection createConnection() {
try {
//1、加载配置文件
InputStream is = ConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
return dataSource.getConnection();
//3、加载驱动
//Class.forName(driverClass);
//获取连接
//Connection connection = DriverManager.getConnection(url, user, password);
//return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static Connection getConnection(){
Connection conn = threadLocal.get();
if (conn == null) {
conn = createConnection();
threadLocal.set(conn);
}
return threadLocal.get();
}
//关闭数据库连接
public static void closeConnection() throws SQLException {
Connection conn = threadLocal.get();
if (conn == null) {
return;
}
if (!conn.isClosed()) {
conn.close();
//threadLocal.set(null);
threadLocal.remove();
}
}
}
【JDBCUtil.java】
package com.javaweb.qqzone.myssm.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class JDBCUtil {
public static Connection getConnection() {
return ConnectionUtil.getConnection();
}
public static void closeResource(Connection connection, PreparedStatement psm, ResultSet rs) {
//由于使用ThreadLocal,则不需要在这里关闭资源
}
}
2.BaseDAO实现对数据库进行CRUD的通用操作
【BaseDAO.java】
package com.javaweb.qqzone.myssm.basedao; import com.javaweb.qqzone.myssm.exception.DAOException; import com.javaweb.qqzone.myssm.util.JDBCUtil; import java.lang.reflect.Constructor; import java.lang.reflect.Field; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.List; ; public abstract class BaseDAO{ private Class clazz = null; { //获取当前BaseDAO的子类继承的父类中的泛型 Type genericSuperclass = this.getClass().getGenericSuperclass(); ParameterizedType type = (ParameterizedType) genericSuperclass; Type[] typeArguments = type.getActualTypeArguments(); clazz = (Class ) typeArguments[0];//泛型的第一个参数 } //通用的增删改操作,使用可变形参-----version2.0 (考虑事务) public int update(String sql, Object... args) { Connection con = null; PreparedStatement psm = null; try { con = JDBCUtil.getConnection(); System.out.println(con); //1、预编译SQL语句，返回PreparedStatement实例 psm = con.prepareStatement(sql); //2、填充占位符 for (int i = 0; i < args.length; i++) { //小心参数的声明错误 psm.setObject(i + 1, args[i]); } //3、执行 return psm.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new DAOException("BaseDAO update出错了！"); } finally { //4、资源的关闭 JDBCUtil.closeResource(con, psm,null); } } public T getInstance(String sql, Object ... args) { Connection con = null; PreparedStatement psm = null; ResultSet rs = null; try { con = JDBCUtil.getConnection(); psm = con.prepareStatement(sql); for (int i = 0; i < args.length; i++) { psm.setObject(i + 1, args[i]); } rs = psm.executeQuery(); //获取结果集的元数据 String name = "Tom"; Tom是关键，String和name是修饰name的两个元数据 ResultSetMetaData rsmd = rs.getMetaData(); //通过ResultSetMetaData获取结果集中的列表 int columnCount = rsmd.getColumnCount(); if (rs.next()) { T t = clazz.newInstance(); //处理结果集一行数据中的每一个列 for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i + 1); //获取每个列的别名 String columnLabel = rsmd.getColumnLabel(i + 1); //给t对象指定的columnName属性赋值columnValue,通过反射 setValue(t, columnLabel, columnValue); } return t; } } catch (Exception e) { e.printStackTrace(); throw new DAOException("BaseDAO 查询出错了！"); } finally { JDBCUtil.closeResource(con, psm, rs); } return null; } private void setValue(Object obj, String property, Object propertyValue) throws Exception{ Class clazz = obj.getClass(); //获取property这个字符传对应的属性名 Field field = clazz.getDeclaredField(property); if (field != null) { //获取当前字段的类型名称 String typeName = field.getType().getName(); //判断如果是自定义类型，则需要调用这个自定义类的带一个参数的构造方法，创建出这个自定义的实例对象，然后将实例对象赋值给这个属性 if (isMyType(typeName)) { //假设typeName是“com.javaweb.qqzone.zone.pojo.UserBasic” Class typeNameClass = Class.forName(typeName); Constructor constructor = typeNameClass.getDeclaredConstructor(java.lang.Integer.class); propertyValue = constructor.newInstance(propertyValue); } } field.setAccessible(true); field.set(obj, propertyValue); } private static boolean isMyType(String typeName) { return !isNotMyType(typeName); } private static boolean isNotMyType(String typeName) { return "java.lang.Integer".equals(typeName) || "java.lang.String".equals(typeName) || "java.util.Date".equals(typeName) || "java.sql.Date".equals(typeName) || "java.lang.Double".equals(typeName); } public List getForList(String sql, Object ... args) { Connection con = null; PreparedStatement psm = null; ResultSet rs = null; try { con = JDBCUtil.getConnection(); psm = con.prepareStatement(sql); for (int i = 0; i < args.length; i++) { psm.setObject(i + 1, args[i]); } rs = psm.executeQuery(); //获取结果集的元数据 String name = "Tom"; Tom是关键，String和name是修饰name的两个元数据 ResultSetMetaData rsmd = rs.getMetaData(); //通过ResultSetMetaData获取结果集中的列表 int columnCount = rsmd.getColumnCount(); //创建集合对象 ArrayList list = new ArrayList<>(); while (rs.next()) { T t = clazz.newInstance(); //处理结果集一行数据中的每一个列;给t对象指定的属性赋值 for (int i = 0; i < columnCount; i++) { Object columnValue = rs.getObject(i + 1); //获取每个列的别名 String columnLabel = rsmd.getColumnLabel(i + 1); //给t对象指定的columnName属性赋值columnValue,通过反射 setValue(t, columnLabel, columnValue); } list.add(t); } return list; } catch (Exception e) { e.printStackTrace(); throw new DAOException("BaseDAO 查询列表出错了！"); } finally { JDBCUtil.closeResource(con, psm, rs); } } public E getValue(String sql, Object ... args) { Connection con = null; PreparedStatement psm = null; ResultSet rs = null; try { con = JDBCUtil.getConnection(); psm = con.prepareStatement(sql); for (int i = 0; i < args.length; i++) { psm.setObject(i + 1, args[i]); } rs = psm.executeQuery(); if (rs.next()) { return (E) rs.getObject(1); } } catch (Exception e) { e.printStackTrace(); throw new DAOException("BaseDAO 查询特殊值出错了！"); } finally { JDBCUtil.closeResource(con, psm, rs); } return null; } }
