- jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/
charset=?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
username=root
password=root
dbname=student
2.连接池数据源SimpleDataSource
package dbTools;
import java.io.*;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;
import javax.sql.DataSource;
public class SimpleDataSource implements DataSource
{
private static int poolSize = 10;
private LinkedList<Connection> pool = new LinkedList<Connection>();
public SimpleDataSource(String driver, String url, String name, String pwd) {
this(driver, url, name, pwd, poolSize);
}
public SimpleDataSource(String driver, String url, String name, String pwd, int poolSize) {
try {
Class.forName(driver);
this.poolSize = poolSize;
if (poolSize <= 0) {
throw new RuntimeException("初始化池大小失败: " + poolSize);
}
for (int i = 0; i < poolSize; i++) {
Connection con = DriverManager.getConnection(url, name, pwd);
con = ConnectionProxy.getProxy(con, pool);// 获取被代理的对象
pool.add(con);// 添加被代理的对象
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
}
}
/** 获取池大小 */
public int getPoolSize() {
return poolSize;
}
/** 不支持日志操作 */
public PrintWriter getLogWriter() throws SQLException {
throw new RuntimeException("Unsupport Operation.");
}
public void setLogWriter(PrintWriter out) throws SQLException {
throw new RuntimeException("Unsupport operation.");
}
/** 不支持超时操作 */
public void setLoginTimeout(int seconds) throws SQLException {
throw new RuntimeException("Unsupport operation.");
}
public int getLoginTimeout() throws SQLException {
return 0;
}
@SuppressWarnings("unchecked")
public <T> T unwrap(Class<T> iface) throws SQLException {
return (T) this;
}
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return DataSource.class.equals(iface);
}
/** 从池中取一个连接对象,使用了同步和线程调度 */
public Connection getConnection() throws SQLException {
synchronized (pool) {
if (pool.size() == 0) {
try {
pool.wait();
} catch (InterruptedException e) {
throw new RuntimeException(e.getMessage(), e);
}
return getConnection();
} else {
return pool.removeFirst();
}
}
}
public Connection getConnection(String username, String password) throws SQLException {
throw new RuntimeException("不支持接收用户名和密码的操作");
}
/** 实现对Connection的动态代理 */
static class ConnectionProxy implements InvocationHandler {
private Object obj;
private LinkedList<Connection> pool;
private ConnectionProxy(Object obj, LinkedList<Connection> pool) {
this.obj = obj;
this.pool = pool;
}
public static Connection getProxy(Object o, LinkedList<Connection> pool) {
Object proxed = Proxy.newProxyInstance(o.getClass().getClassLoader(), new Class[] { Connection.class },
new ConnectionProxy(o, pool));
return (Connection) proxed;
}
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if (method.getName().equals("close")) {
synchronized (pool) {
pool.add((Connection) proxy);
pool.notify();
}
return null;
} else {
return method.invoke(obj, args);
}
}
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException
{
return null;
}
}
3 jdbc封装DBUtil
package dbTools;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import javax.sql.DataSource;
public class DBUtil
{
private static String USERNAME = null;
private static String PASSWORD = null;
private static String DRIVER = null;
private static String URL = null;
public static String DBNAME = null;
public static String CHARSET = null;
private static Connection conn = null;
private PreparedStatement pst = null;
private ResultSet rs = null;
private static DataSource dataSource = null;
private static final boolean AUTO_COMMIT = true;
private String SQLTables = "show tables";
static
{
// 创建一个 Properties对象
Properties pro = new Properties();
try
{
// 通过类加载器将配置文件加载进来
pro.load(DBUtil.class.getClassLoader()
.getResourceAsStream("jdbc.properties"));
// 读取配置文件得到属性值
DBNAME=pro.getProperty("dbname");
USERNAME = pro.getProperty("username");
PASSWORD = pro.getProperty("password");
DRIVER = pro.getProperty("driver");
CHARSET=pro.getProperty("charset");
URL = pro.getProperty("url")+DBNAME+CHARSET;
System.out.println(URL);
// 加载驱动
dataSource = new SimpleDataSource(DRIVER, URL, USERNAME, PASSWORD);
conn = dataSource.getConnection();
if (!AUTO_COMMIT)
conn.setAutoCommit(AUTO_COMMIT);
} catch (IOException e)
{
e.printStackTrace();
} catch (SQLException e)
{
e.printStackTrace();
}
}
/**
*
* @param sql 增删改sql语句
* @return 更新成功返回true
*/
public boolean update(String sql)
{
int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
try
{
pst = conn.prepareStatement(sql);
result = pst.executeUpdate();
} catch (SQLException e)
{
e.printStackTrace();
}
return result > 0;
}
/**
* @param sql 增删改sql语句
* @param params 给sql中?传值,个数必须和sql中?匹配,
* @return 更新成功返回true
*/
public boolean update(String sql, List<?> params)
{
// 表示当用户执行添加删除和修改的时候所影响数据库的行数
int result = -1;
try
{
pst = conn.prepareStatement(sql);
int index = 1;
// 填充sql语句中的占位符
if (params != null && !params.isEmpty())
{
for (int i = 0; i < params.size(); i++)
{
pst.setObject(index++, params.get(i));
}
}
result = pst.executeUpdate();
} catch (SQLException e)
{
e.printStackTrace();
}
return result > 0;
}
/**
* @param sql 查询 sql语句
* @return 成功返回ResultSet
*/
public ResultSet query(String sql)
{
try
{
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();// 返回查询结果
} catch (SQLException e)
{
e.printStackTrace();
}
return rs;
}
/**
* @param sql 查询sql语句
* @param params 给sql中?传值,个数必须和sql中?匹配,
* @return 成功返回ResultSet
*/
public ResultSet query(String sql, List<?> params)
{
try
{
int index = 1;
pst = conn.prepareStatement(sql);
if (params != null && !params.isEmpty())
{
for (int i = 0; i < params.size(); i++)
{
pst.setObject(index++, params.get(i));
}
}
rs = pst.executeQuery();// 返回查询结果
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
/**
* 关闭对象
* @return
*/
public void Close()
{
if (rs != null)
{
try
{
rs.close();
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (pst != null)
{
try
{
pst.close();
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null)
{
try
{
conn.close();
} catch (SQLException e)
{
System.out.println("关闭数据库连接失败");
}
}
}
/**
* 查询当前数据库所有表名称
* @return list
*/
public List<String> getTables()
{
List<String> list = new ArrayList<String>();
try
{
PreparedStatement ps = conn.prepareStatement(SQLTables);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
String tableName = rs.getString(1);
list.add(tableName);
}
Close();
} catch (SQLException e)
{
System.out.println("数据库查询表名失败了!");
}
return list;
}
/**
* 根据表名返回字段属性
* @param tableName 表名
* @return
*/
public List<String[]> getColumnDatas(String tableName)
{
String SQLColumns = "select column_name ,data_type,column_comment,numeric_scale,numeric_precision from information_schema.columns where table_name = '"
+ tableName + "' " + "and table_schema = '" + DBNAME + "'";
List<String[]> columnList = new ArrayList<String[]>();
try
{
PreparedStatement ps = conn.prepareStatement(SQLColumns);
ResultSet rs = ps.executeQuery();
while (rs.next())
{
String name = rs.getString(1);
String type = rs.getString(2);
String comment = rs.getString(3);
String scale = rs.getString(4);
String precision = rs.getString(5);
type = getType(type, precision, scale);
columnList.add(new String[] { name, type, comment });
}
Close();
} catch (SQLException ex)
{
System.out.println(ex.getMessage());
}
return columnList;
}
/**
* 根据sql字段属性转换为java数据类型
* @param dataType
* @param precision 精度
* @param scale 大小
* @return java类型
* @return
*/
public String getType(String dataType, String precision, String scale)
{
dataType = dataType.toLowerCase();
if (dataType.contains("char") || dataType.contains("text"))
dataType = "String";
else if (dataType.contains("bit"))
dataType = "Boolean";
else if (dataType.contains("bigint"))
dataType = "Long";
else if (dataType.contains("int"))
dataType = "Integer";
else if (dataType.contains("float"))
dataType = "Float";
else if (dataType.contains("double") || dataType.contains("real"))
dataType = "Double";
else if (dataType.contains("number"))
{
if ((scale.length() > 0) && (Integer.parseInt(scale) > 0))
dataType = "Double";
else if ((precision.length() > 0)
&& (Integer.parseInt(precision) > 6))
dataType = "Long";
else
dataType = "Integer";
}
else if (dataType.contains("decimal"))
dataType = "Double";
else if (dataType.contains("date"))
dataType = "java.util.Date";
else if (dataType.contains("time"))
dataType = "java.sql.Timestamp";
else if (dataType.contains("clob"))
dataType = "java.sql.Clob";
else
{
dataType = "Object";
}
return dataType;
}
/**
* 根据列名生成set方法名称
* @param colName
* @return
* @return
*/
private String createSetMethod(String colName)
{
return "set"+colName.substring(0, 1).toUpperCase()+ colName.substring(1);
}
/**
* 把Resultset结果集转换为实体对象List
* @方法名 :rsToEntityList<br>
* @方法描述 :根据结果集(多条数据)映射 到 实体类集合<br>
* @param <T> 泛型
* @param clazz 实体类的Class
* @param rs 查询的结果集
* @return 返回类型 :List<T>
*/
public <T> List<T> rsToEntityList(Class<T> clazz, ResultSet rs)
{
ResultSetMetaData rsmd = null;
List<T> list = new ArrayList<T>();
String temp = "";
Method s = null;
T t = null;
try
{
rsmd = rs.getMetaData();
while (rs.next())
{
t = clazz.newInstance();
for (int i = 1; i <= rsmd.getColumnCount(); i++)
{
temp = rsmd.getColumnName(i);
String dataType = getType(rsmd.getColumnTypeName(i),
String.valueOf(rsmd.getPrecision(i)),
String.valueOf(rsmd.getScale(i)));
if (dataType.equals("Integer"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),Integer.class);
s.invoke(t, rs.getInt(temp));
}
else if (dataType.equals("String"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
String.class);
s.invoke(t, rs.getString(temp));
}
else if (dataType.equals("Boolean"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
Boolean.class);
s.invoke(t, rs.getBoolean(temp));
}
else if (dataType.equals("Long"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
Long.class);
s.invoke(t, rs.getLong(temp));
}
else if (dataType.equals("Float"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
Float.class);
s.invoke(t, rs.getFloat(temp));
}
else if (dataType.equals("Double"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
Double.class);
s.invoke(t, rs.getDouble(temp));
}
else if (dataType.equals("java.util.Date"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
java.util.Date.class);
s.invoke(t, rs.getDate(temp));
}
else if (dataType.equals("Object"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
Object.class);
s.invoke(t, rs.getObject(temp));
}
else if (dataType.equals("java.sql.Clob"))
{
s = clazz.getDeclaredMethod(createSetMethod(temp),
java.sql.Clob.class);
s.invoke(t, rs.getClob(temp));
}
}
list.add(t);
}
} catch (SQLException e)
{
e.printStackTrace();
} catch (IllegalArgumentException e)
{
e.printStackTrace();
} catch (IllegalAccessException e)
{
e.printStackTrace();
} catch (InvocationTargetException e)
{
e.printStackTrace();
} catch (SecurityException e)
{
e.printStackTrace();
} catch (NoSuchMethodException e)
{
e.printStackTrace();
} catch (InstantiationException e)
{
e.printStackTrace();
}
return list;
}
}
4.测试
package dbTools;
import java.util.*;
import yzh.ssm.model.Stu;
public class Test
{
public static void main(String[] args)
{
DBUtil db=new DBUtil();
String sql="select * from Stu";
List<Stu> list=db.rsToEntityList(Stu.class, db.query(sql));
System.out.println(list);
}
}