简单是记录使用queryrunner的步骤和方法,及响应的工具类,都在这里,开发简单的servlet程序时候,使用起来比较方便,
以上我的demo结构
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
//获取连接
public class JDBCUtils {
public static DataSource getDataSource() {
InputStream in=JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
DataSource datasource=null;
Connection conn=null;
Properties pro=new Properties();
try {
pro.load(in);
} catch (IOException e) {
throw new RuntimeException("加载配置资源失败,请检查路径!");
}
try {
datasource=DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
throw new RuntimeException("获取数据源失败!");
}
return datasource;
}
public static Connection getConnection() {
InputStream in=JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties");
DataSource datasource=null;
Connection conn=null;
Properties pro=new Properties();
try {
pro.load(in);
} catch (IOException e) {
throw new RuntimeException("加载配置资源失败,请检查路径!");
}
try {
datasource=DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
throw new RuntimeException("获取数据源失败!");
}
try {
conn=datasource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("获取连接失败!");
}
try {
in.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(Connection conn, PreparedStatement pres) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pres!=null) {
try {
pres.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(Connection conn,Statement pres) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pres!=null) {
try {
pres.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
package com.utils;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
public class MyDbUtils {
private static QueryRunner queryrunner=null;
private static DataSource datasource=JDBCUtils.getDataSource();
static {
queryrunner=new QueryRunner(datasource);
}
public static <T>T select(String sql,Object[] params,T t) {
try {
return queryrunner.query(sql, new BeanHandler<T>((Class<T>) t.getClass()),params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static <T>List<T> selectList(String sql,Object[] params,T t){
try {
return queryrunner.query(sql,new BeanListHandler<T>((Class<T>)t.getClass()),params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static int execute(String sql,Object ...parms) {
try {
return queryrunner.update(sql, parms);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return -1;
}
public static void close() {
try {
datasource.getConnection().close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
使用demo
public class TeacherDao {
public final String rename="T# Tno,Tname,Age age,Title title";
//根据教师号登录
public Teacher findByTno(String tno){
String sql="select "+rename+" from t_teacher where T#=?";
Object[]parms={tno};
return MyDbUtils.select(sql,parms,new Teacher());
}
}
即可
druid.properties
# druid.properties\u6587\u4EF6\u7684\u914D\u7F6E
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433;databaseName=TeachingDB_douhuaibao
username=sa
password=123456
使用到的jar包
commons-beanutils-1.8.3.jar
druid-1.1.10.jar
commons-dbutils-1.4.jar