例1、 通过DBCP三种方式创建连接池。
第O步、导包,建包
方法一、TestDbcp01.java
package cn.itcast.dbcp.dbcp01;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbcp.BasicDataSource;
public class TestDbcp01 {
public static void main(String[] args) {
/*
* BasicDataSource implements javax.sql.DataSource
* 数据源 连接池
*/
BasicDataSource bds=new BasicDataSource();
//设置用户名,和密码
bds.setUsername("root");
bds.setPassword("root");
//设置url
bds.setUrl("jdbc:mysql://localhost:3306/test");
//设置驱动类
bds.setDriverClassName("com.mysql.jdbc.Driver");
//设置初始化连接数
bds.setInitialSize(4);
//设置最大连接数
bds.setMaxActive(10);
//设置最大空闲数
bds.setMaxIdle(5);
//设置最小空闲数
bds.setMinIdle(3);
//设置最大等待时间,以毫秒为单位
bds.setMaxWait(5*1000);
Connection conn=null;
try {
//获取连接
conn= bds.getConnection();
System.out.println(conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
if(conn!=null){
//把连接放回到连接池中
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
方法二TestDbcp02.java
package cn.itcast.dbcp02;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class TestDbcp02 {
public static void main(String[] args) {
Connection conn=null;
try {
Properties properties=new Properties();
properties.setProperty("username", "root");
properties.setProperty("password", "root");
properties.setProperty("url", "jdbc:mysql://localhost:3306/test");
properties.setProperty("driverClassName", "com.mysql.jdbc.Driver");
properties.setProperty("initialSize", "4");
properties.setProperty("maxActive", "10");
properties.setProperty("maxIdle", "5");
properties.setProperty("minIdle", "3");
properties.setProperty("maxWait", "5000");
//使用BasicDataSourceFactory创建数据源
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
conn=dataSource.getConnection();
System.out.println(conn);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}finally{
try {
if(conn!=null){
//把连接放回到连接池中
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
方法三、
第一步、jdbc.properties
username=root
password=root
diverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
initialSize=4
maxActive=8
maxIdle=7
minIdle=3
maxWait=5000
第二步、TestDbcp03.java
package cn.itcast.dbcp03;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class TestDbcp03 {
public static void main(String[] args) {
Connection conn=null;
try {
//获取jdbc.properties文件的信息
//getClassLoader()获取文件路径,查找文件
InputStream inStream=TestDbcp03.class.getClassLoader().getResourceAsStream("cn/itcast
/dbcp03/jdbc.properties");
/*上面一行等同于:(TestDbcp03.class当前所在的路径,查找文件)
InputStream inStream=TestDbcp03.class.getResourceAsStream("jdbc.properties");
*/
Properties properties=new Properties();
properties.load(inStream);
System.out.println(properties.getProperty("username"));
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
conn=dataSource.getConnection();
System.out.println(conn);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}finally{
try {
if(conn!=null){
//把连接放回到连接池中
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
例2、通过C3P0获取JDBC连接池。
方法一、
package cn.itcast.c3p0;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TestC3p0 {
public static void main(String[] args) {
ComboPooledDataSource cpds = new ComboPooledDataSource();
Connection conn=null;
try {
//设置驱动类
cpds.setDriverClass("com.mysql.jdbc.Driver"); //loads the jdbc driver
//设置url
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
//设置用户名和密码
cpds.setUser("root");
cpds.setPassword("root");
//设置最小连接数
cpds.setMinPoolSize(5);
//设置每次请求连接的数目
cpds.setAcquireIncrement(5);
//设置最大连接数
cpds.setMaxPoolSize(20);
//获取连接
conn = cpds.getConnection();
System.out.println(conn);
PreparedStatement pstatement=conn.prepareStatement("select * from employees");
ResultSet rs=pstatement.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+":"+rs.getString("username"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
运行结果:
2012-10-18 18:53:00 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
2012-10-18 18:53:01 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.2-pre2 [built 18-May-2012 10:14:10 -0400; debug? true; trace: 10]
2012-10-18 18:53:01 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [acquireIncrement ……
com.mchange.v2.c3p0.impl.NewProxyConnection@baa466
1:fandong
10:deng
11:zhangsan
12:denghongmei
13:fandong2
14:fandogn
例3、工具类的书写。
第一步、书写工具类.
package cn.itcast.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp.BasicDataSource;
public class DBManager {
/**静态的变量是所有对象共有的,只在类执行的时候加载一次,这种算法很重要**/
private static DBManager dbManager=new DBManager();
private BasicDataSource bds=null;
private DBManager(){
bds=new BasicDataSource();
//设置用户名,和密码
bds.setUsername("root");
bds.setPassword("root");
//设置url
bds.setUrl("jdbc:mysql://localhost:3306/test");
//设置驱动
bds.setDriverClassName("com.mysql.jdbc.Driver");
//设置初始化连接数
bds.setInitialSize(4);
//设置最大连接数
bds.setMaxActive(10);
//设置最大空闲数
bds.setMaxIdle(5);
//设置最小空闲数
bds.setMinIdle(3);
//设置最大等待时间,以毫秒为单位
bds.setMaxWait(5000);
}
/*
* 外部程序获得BDManager对象的方法
*/
public static DBManager getDbManager(){
return dbManager;
}
/*
* 获取连接
*/
public Connection getConnection(){
Connection conn=null;
try {
conn = bds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//关闭资源
public void closeResource(Connection conn,Statement statement,ResultSet rs){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(statement!=null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
第二步、测试工具类。
package cn.itcast.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestDBManager {
public static void main(String[] args) {
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
ResultSet rs=null;
//获取DBManager 的实例,此时会调用私有的构造方法
dbManager = DBManager.getDbManager();
//获取连接
conn=dbManager.getConnection();
/*
* 无论上面的连接创建几个(conn1,conn2......)DBManager的私有构造方法只调用一次,
* 即只创建一个私有的dbManager(一个连接池)
*/
System.out.println(conn);
try {
pstatement=conn.prepareStatement("select * from employees");
rs=pstatement.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+":"+rs.getString("username"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(dbManager!=null){
//获取连接到连接池中
dbManager.closeResource(conn, pstatement, rs);
}
}
}
}
运行结果:
org.apache.commons.dbcp.PoolableConnection@193722c
1:fandong
10:deng
11:zhangsan
12:denghongmei
13:fandong2
14:fandogn
例4、在web服务器tomcat中配置连接池
第一种、配置一个通用的连接池,任何web应用都能访问。
第一步、在tomcat文档中,找到JNDI中配置连接池的方法,及相关代码
<Resource name="jdbc/EmployeeDB"
auth="Container"
type="javax.sql.DataSource"
username="dbusername"
password="dbpassword"
driverClassName="org.hsql.jdbcDriver"
url="jdbc:HypersonicSQL:database"
maxActive="8"
maxIdle="4"/>
第二步、tomcat所在目录confcontext.xml 打开
第三步、在context.xml中的<Context></Context>
标签对之间加入上面代码,并改掉相关标签内容如下!!!
<Resource name="jdbc/mysql"#外部访问的名称,自定义,建议使用jdbc/xxx的命名方式
auth="Container"#表示由tomcat容器来创建和管理这个连接池
#若为auth="Application.Container"则表示由web应用程序来创建和管理Resource
type="javax.sql.DataSource"#指定Resource所属的java类名(这里取值为
#type="javax.sql.DataSource",声明为数据库连接池)
username="root"#用户名
password="root"#密码
driverClassName="com.mysql.jdbc.Driver"#驱动类
url="jdbc:mysql://localhost:3306/test"#url路径
maxActive="8"#设置最大连接数
maxIdle="7" #最大空闲数
initialSize="4"#初始化连接数
minIdle="3" #最小空闲数
/>
这样,就由tomcat创建出了一个tomcat连接池了
第四步、注意要将驱动放到tomcat的lib包中,即是将mysql-connector-java-5.1.10-bin.jar放到lib包中,否则服务器无法加载驱动.
第五步、在jsp中使用,测试:完成以上步骤后完成以下工作:
Test.jsp
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ page import="javax.naming.Context"%>
<%@ page import="javax.naming.InitialContext"%>
<%@ page import="javax.sql.DataSource"%>
<%@ page import="java.sql.*"%>
<%
//是执行命名操作的初始化上下文,初始化命名上下文
Context initCtx = new InitialContext();
//java:comp/env 写法固定,表示java的环境命名上下文,可以把java的环境命名上下文看成是tomcat
//服务器的空间中的共有区域,利用初始化上下文对象查找java的环境命名上下文
Context envCtx = (Context) initCtx.lookup("java:comp/env");
//下面打印的结果为:envCtx org.apache.naming.NamingContext@1edc290,即上面Context的实现类
//System.out.println("envCtx"+" "+envCtx);
//在java的环境命名上下文空间中通过(jdbc/mysql)查找连接池对象,强制转换为type="javax.sql.DataSource"指定的类型
DataSource ds = (DataSource)envCtx.lookup("jdbc/mysql");
try{
//获取连接
Connection conn = ds.getConnection();
String sql="SELECT id,username,realname FROM employees WHERE id=1";
PreparedStatement pstatement=conn.prepareStatement(sql);
ResultSet rs=pstatement.executeQuery();
while(rs.next()){
out.println(rs.getInt(1)+":"+rs.getString(2)+":"+rs.getString(3));
}
}catch(Exception e){
e.printStackTrace();
}
%>
运行结果:
在IE中打印出:
1:fandong:发到
第二种、配置一个当前应用的web应用的连接池
第一步、清除上面的痕迹,清除context.xml文件中的<Resource/>
标签
第二步、为工程配置虚拟目录,tomcat目录->conf->Catalina->localhost->day14_DBSource.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context docBase="F:\Lianxi\day14_DBSource\WebRoot" reloadable="true">
<Resource name="jdbc/mysql"
auth="Container"
type="javax.sql.DataSource"
username="root"
password="root"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test"
maxActive="8"
maxIdle="7"
initialSize="4"
minIdle="3"
/>
</Context>
注意:docBase:表示web应用的绝对路径,应该指向F:\Lianxi\day14_DBSource\WebRoot
reloadable=”true”表示web应用中的类发生变化的时候,服务器自动加载!!!
第三步、注意要将驱动放到tomcat的lib包中,即是将mysql-connector-java-5.1.10-bin.jar放到lib包中,否则服务器无法加载驱动.
第四步、通过http://localhost:8080/day14_DBSource/test.jsp访问
dbutils实例
1、dbutils
是对jdbc最简单的封装。
例1、简单的dbutils的实例。
第一步、
package cn.itcast.test;
import *;
public class TestSelect {
public static void main(String[] args) {
//testjdbcSelect();
testDBUtilSelect();
}
/*
* 通过dbutils操作数据库
*/
private static void testDBUtilSelect() {
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
String sql="select id,name,sex from test1";
try {
QueryRunner query=new QueryRunner();
List<Person> list = (List)query.query(conn, sql,new BeanListHandler(Person.class));
for(Person p:list){
System.out.println(p.getId()+":"+p.getName());
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null){
dbManager.closeResource(conn, null, null);
}
}
}
/*
* 传统的jdbc实现
*/
private static void testjdbcSelect() {
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
ResultSet rs=null;
PreparedStatement pstatement=null;
String sql="select id,name,sex from test1";
try {
pstatement=conn.prepareStatement(sql);
rs=pstatement.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+":"+rs.getString(2)+":"+rs.getString(3));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null){
dbManager.closeResource(conn, pstatement, rs);
}
}
}
}
第二步、
package cn.itcast.test;
public class Person {
private String id;
private String name;
private String sex;
……
例2、dbutil执行查询语句。
第一步、
package cn.itcast.test;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import cn.itcast.util.DBManager;
public class TestSelect02 {
public static void main(String[] args) {
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
String sql="select id,name,sex from test1";
try {
/*
* 执行sql的类,该类中提供了执行select insert update delete语句的方法
*/
QueryRunner query=new QueryRunner();
/*
* query(conn,sql,rsh)
* *第一个参数 连接对象
* *第二个参数 执行的sql语句
* *第三个参数
*/
Object obj = query.query(conn, sql,new ResultSetHandlerImpl());
System.out.println("obj"+":"+obj);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
第二步、
package cn.itcast.test;
import *;
public class ResultSetHandlerImpl implements ResultSetHandler {
@Override
public Object handle(ResultSet rs) throws SQLException {
System.out.println("执行********handle(ResultSet rs)");
String str ="fandong";
return str;
}
}
对上面的进行改进:
第一步、
package cn.itcast.test;
import *;
public class ResultSetHandlerImpl implements ResultSetHandler {
@Override
public Object handle(ResultSet rs) throws SQLException {
System.out.println("执行********handle(ResultSet rs)");
Person person=new Person();
person.setId("1");
person.setName("fandong");
person.setSex("男");
return person;
}
}
第二步、
package cn.itcast.test;
import *;
public class TestSelect02 {
public static void main(String[] args) {
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
String sql="select id,name,sex from test1";
try {
/*
* 执行sql的类,该类中提供了执行select insert update delete语句的方法
*/
QueryRunner query=new QueryRunner();
/*
* query(conn,sql,rsh)
* *第一个参数 连接对象
* *第二个参数 执行的sql语句
* *第三个参数
* *ResultSetHandler实现类接口的返回值就是query方法的返回值
*/
Object obj = query.query(conn, sql,new ResultSetHandlerImpl());
Person person=(Person)obj;
System.out.println(person.getId()+":"+person.getName()+":"+person.getSex());
System.out.println("obj"+":"+obj);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
运行结果为:
执行********handle(ResultSet rs)
1:fandong:男
obj:cn.itcast.test.Person@187814
例3、dbutils执行查询语句。
第一步、建表
第二步、ResultHandler的实现类
package cn.itcast.test;
import *;
public class ResultSetHandlerImpl implements ResultSetHandler {
@Override
public Object handle(ResultSet rs) throws SQLException {
List<Person> list=new ArrayList<Person>();
while(rs.next()){
Person p=new Person();
p.setId(rs.getInt(1)+"");
p.setName(rs.getString(2));
p.setSex(rs.getString(3));
list.add(p);
}
return list;
}
}
第三步、TestSelect02.java
package cn.itcast.test;
import *;
public class TestSelect02 {
public static void main(String[] args) {
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
String sql="select id,name,sex from test1";
try {
/*
* query(conn,sql,rsh)
* *第一个参数 连接对象
* *第二个参数 执行的sql语句
* *第三个参数 转化结果集-->对象 ResultSetHandler
* query.query(conn, sql,new ResultSetHandlerImpl())
* *该方法的返回值就是ResultSetHandler实现类接口的返回值
* *ResultSetHandler实现类接口的方法public Object handle(ResultSet rs) * 是底层开发者调用,不需要程序员自己调用
*/
QueryRunner query=new QueryRunner();
Object obj = query.query(conn, sql,new ResultSetHandlerImpl());
System.out.println("obj"+":"+obj);
List<Person> list=(List<Person>)obj;
for(Person p:list){
System.out.println(p.getId()+":"+p.getName()+":"+p.getSex());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
注意:第一步中handle(ResultSet rs)方法的返回值的类型与第二步中
Object obj = query.query(conn, sql,new ResultSetHandlerImpl()); 返回值的类型相同!!!
所以有下列代码:
在开发中常用的写法:
package cn.itcast.test;
import *;
public class TestSelect03 {
public static void main(String[] args) {
/******************************************************************************/
//无人的角楼
ResultSetHandler sesultSetHandler=new ResultSetHandler(){
@Override
public Object handle(ResultSet rs) throws SQLException {
List<Person> list=new ArrayList<Person>();
while(rs.next()){
Person p=new Person();
p.setId(rs.getInt(1)+"");
p.setName(rs.getString(2));
p.setSex(rs.getString(3));
list.add(p);
}
return list;
}
};
/******************************************************************************/
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
String sql="select id,name,sex from test1";
try {
QueryRunner query=new QueryRunner();
Object obj = query.query(conn, sql,sesultSetHandler);
System.out.println("obj"+":"+obj);
List<Person> list=(List<Person>)obj;
for(Person p:list){
System.out.println(p.getId()+":"+p.getName()+":"+p.getSex());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
例3、带参数(条件的查询)。
注意:掌握三个接口的使用方法:ResultSetHandler,BeanListHandler,BeanHandler
package cn.itcast.test;
import *;
public class TestSelect04 {
public static void main(String[] args) {
//testSelectWithParams();
testSelectWithBeanHandlerParams();
}
/**********************************************************************************/
public static void testSelectWithBeanHandlerParams(){
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
//组织sql语句
String sql="select id,name,sex from test1 where id=?";
//设置参数值
Object[] params=new Object[1];
params[0]=10;
Class clazz=Person.class;
try {
QueryRunner query=new QueryRunner();
Object obj = query.query(conn,sql,new BeanHandler(clazz),params);
Person p=(Person)obj;
System.out.println(p.getId()+":"+p.getName()+":"+p.getSex());
} catch (SQLException e) {
e.printStackTrace();
}
}
/**********************************************************************************/
public static void testSelectWithBeanListHandlerParams(){
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
String name="张";
String sex="男";
//组织sql语句
String sql="select id,name,sex from test1 where name like ? and sex=?";
//设置参数值
Object[] params=new Object[2];
params[0]="%"+name+"%";
params[1]=sex;
Class clazz=Person.class;
try {
QueryRunner query=new QueryRunner();
Object obj = query.query(conn,sql,new BeanListHandler(clazz),params);
System.out.println("obj"+":"+obj);
List<Person> list=(List<Person>)(obj);
for(Person p:list){
System.out.println(p.getId()+":"+p.getName()+":"+p.getSex());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**********************************************************************************/
public static void testSelectWithBeanListHandler(){
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
//组织sql语句
String sql="select id,name,sex from test1";
Class clazz=Person.class;
try {
QueryRunner query=new QueryRunner();
/*
* BeanListHandler():查询数据库中的数据放到一个javabean中,javabean放置到list中
* 注意事项:
* * 该类构造方法的参数为Class类型,给Class类型必须是封装数据的javabean
* * 该javabean中的属性必须和数据库中的字段的名称相同,不区分大小写
* Person类 id name sex
* person表 id name sex 要对应
* * 该javabean中的属性如果和数据库中的字段的名称不相同,就取不到值,取到null
* * 该javabean中的属性的类型也必须和数据库中的字段的类型相同
*/
Object obj = query.query(conn,sql,new BeanListHandler(clazz));
System.out.println("obj"+":"+obj);
List<Person> list=(List<Person>)(obj);
for(Person p:list){
System.out.println(p.getId()+":"+p.getName()+":"+p.getSex());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**********************************************************************************/
public static void testSelectWithParams() {
/********************************************************************/
ResultSetHandler resultSetHandler=new ResultSetHandler(){
@Override
public Object handle(ResultSet rs) throws SQLException {
List<Person> list=new ArrayList<Person>();
while(rs.next()){
Person p=new Person();
p.setId(rs.getInt(1)+"");
p.setName(rs.getString(2));
p.setSex(rs.getString(3));
list.add(p);
}
return list;
}
};
/********************************************************************/
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
String username="张";
String sex="男";
//组织sql语句
String sql="select id,name,sex from test1 where name like ? and sex=?";
//设置参数
Object[] params=new Object[2];
//千万注意,下面的params[0]赋值时千万不能加单引号,否则查不出来
//带条件查询,用preparedStatement时,条件内不要出现单引号
//设置参数时,参数的顺序和sql语句中?的位置要对应
params[0]="%"+username+"%";
params[1]=sex;
try {
QueryRunner query=new QueryRunner();
Object obj = query.query(conn,sql,resultSetHandler,params);
System.out.println("obj"+":"+obj);
List<Person> list=(List<Person>)(obj);
for(Person p:list){
System.out.println(p.getId()+":"+p.getName()+":"+p.getSex());
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn,null,null);//只需要关闭连接就行了,结果集在底层代码就关闭了
}
}
}
例4、增删改查。
package cn.itcast.test;
import *;
public class TestDML {
public static void main(String[] args) {
testInsert();
testUpdate();
testDelete();
}
public static void testDelete(){
//插入数据到Person表中
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
//住址Sql语句
String sql="delete from test1 where id=?";
//设置参数
Object[] params=new Object[]{12};
QueryRunner query=new QueryRunner();
try {
query.update(conn, sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
}
public static void testUpdate(){
//插入数据到Person表中
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
//住址Sql语句
String sql="update test1 set name=?,sex=? where id=?";
//设置参数
Object[] params=new Object[]{"樊冬","男",11};
QueryRunner query=new QueryRunner();
try {
query.update(conn, sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
}
public static void testInsert(){
//插入数据到Person表中
DBManager dbManager=null;
Connection conn=null;
dbManager=DBManager.getDbManager();
conn=dbManager.getConnection();
//住址Sql语句
String sql="insert into test1(id,name,sex) values(null,?,?)";
//设置参数
Object[] params=new Object[]{"邓红梅","女"};
QueryRunner query=new QueryRunner();
try {
query.update(conn, sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
}
}
例5、dbutil练习之增删改查,以及查询
第一步、TestDaoEmployees.java
package cn.itcast.employees;
import java.util.List;
import org.junit.Ignore;
import org.junit.Test;
/*
* 测试对employees的表的crud
*/
public class TestDaoEmployees {
/*
* 保存员工信息
*/
@Test @Ignore public void saveEmployee(){
DaoEmployeesImpl daoEmployeesImpl=new DaoEmployeesImpl();
Employee employee=new Employee();
employee.setUsername("zhangsan");
employee.setPassword("123");
employee.setRealname("李四");
employee.setSex("女");
employee.setEdu("本科");
employee.setDescription("很好的工作");
employee.setMajor("电子");
employee.setBirthday(java.sql.Date.valueOf("1990-01-01"));
employee.setHredate(java.sql.Date.valueOf("2009-09-09"));
employee.setRole("总经理");
daoEmployeesImpl.saveEmployees(employee);
}
/*
* 更新员工信息
*/
@Test @Ignore public void updateEmployee(){
DaoEmployeesImpl daoEmployeesImpl=new DaoEmployeesImpl();
Employee employee=new Employee();
employee.setId(10);
employee.setUsername("zhangsan");
employee.setPassword("123");
employee.setRealname("李四");
employee.setSex("女");
employee.setEdu("本科");
employee.setDescription("很好的工作");
employee.setMajor("电子");
employee.setBirthday(java.sql.Date.valueOf("1990-01-01"));
employee.setHredate(java.sql.Date.valueOf("2009-09-09"));
employee.setRole("总经理");
daoEmployeesImpl.updateEmployees(employee);
}
/*
* 删除员工信息
*/
@Test @Ignore public void deleteEmployee(){
DaoEmployeesImpl daoEmployeesImpl=new DaoEmployeesImpl();
daoEmployeesImpl.deleteEmployees(14);
}
@Test @Ignore public void findEmployeeById(){
DaoEmployeesImpl daoEmployeesImpl=new DaoEmployeesImpl();
Integer id=11;
Employee employee = daoEmployeesImpl.findEmployeeById(id);
System.out.println(employee.getUsername());
}
/*
* 支持模糊查询
*/
@Test @Ignore public void findEmployeeByCondition(){
DaoEmployeesImpl daoEmployeesImpl=new DaoEmployeesImpl();
String name="张";
String sex="女";
List<Employee> list = daoEmployeesImpl.findEmployeeByCondition(name,sex);
for(Employee employee:list){
System.out.println(employee.getId()+":"+employee.getRealname());
}
}
/*
* 支持全部查询
*/
@Test public void findEmployee(){
DaoEmployeesImpl daoEmployeesImpl=new DaoEmployeesImpl();
List<Employee> list=daoEmployeesImpl.findEmployee();
for(Employee employee:list){
System.out.println(employee.getId()+":"+employee.getRealname());
}
}
}
第二步、Employee.java
package cn.itcast.employees;
import java.sql.Date;
/*
* 该javabean中的属性要和表中的字段名称相同
*/
public class Employee {
private Integer id;
private String username;
private String password;
private String realname;
private String sex;
private Date birthday;
private String edu;
private String major;
private String description;
private Date hredate;
private String role;
public Integer getId() {
return id;
}
……
第三步、DaoEmployeesImpl.java
package cn.itcast.employees;
import *;
public class DaoEmployeesImpl extends BaseDao{
public void saveEmployees(Employee employee) {
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
//组织sql语句
String sql=
"insert into employees(id,username,pasw,realname,sex,birthday,edu,major,des,hiredate,role) values(null,?,?,?,?,?,?,?,?,?,?)";
//设置参数
Object[] param=new Object[]{employee.getUsername(),employee.getPassword(),employee.getRealname(),employee.getSex(),employee.getBirthday(), employee.getEdu(),employee.getMajor(),employee.getDescription(),employee.getHredate(),employee.getRole()};
//实例化queryRunner
try {
//调用update方法执行insert
super.update(conn,sql,param);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}//连接在这里关,不要再BaseDao中关
}
/*
* 通过id更新员工数据
*/
public void updateEmployees(Employee employee) {
// TODO Auto-generated method stub
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
//组织sql语句
String sql="update employees set username=?,pasw=?,realname=?,sex=?,birthday=?,edu=?,major=?,des=?,hiredate=?,role=? where id=?";
//设置参数
Object[] param=new Object[]{employee.getUsername(),employee.getPassword(),
employee.getRealname(),employee.getSex(),employee.getBirthday(),employee.getEdu(),
employee.getMajor(),employee.getDescription(),employee.getHredate(),employee.getRole(),employee.getId()};
//调用父类方法
try {
super.update(conn, sql, param);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
}
/*
* 通过id删除
*/
public void deleteEmployees(int id) {
// TODO Auto-generated method stub
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
String sql="delete from employees where id=?";
Object[] param=new Object[]{id};
try {
super.update(conn, sql, param);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
}
/*
* 通过id查询员工信息
*/
public Employee findEmployeeById(Integer id) {
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
Employee employee=null;
//组织sql语句
String sql="select id,username,pasw,realname,sex,birthday,edu,major,des,hiredate," +
"role from employees where id=?";
//设置参数
Object[] param=new Object[]{id};
//设置存放信息的javabean
Class clazz=Employee.class;
//执行查询
try {
employee = (Employee)super.findObjectById(conn,sql,clazz,param);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
return employee;
}
/*
* 模糊查询
*/
public List<Employee> findEmployeeByCondition(String name, String sex) {
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
Employee employee=null;
List<Employee> list=null;
String sql="select id,username,pasw,realname,sex,birthday,edu,major,des,hiredate," +
"role from employees where realname like ? and sex=?";
Object[] param=new Object[]{"%"+name+"%",sex};
Class clazz=Employee.class;
try {
list = (List)super.findObjectByCondition(conn,sql,clazz,param);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
return list;
}
public List<Employee> findEmployee() {
DBManager dbManager=DBManager.getDbManager();
Connection conn=dbManager.getConnection();
Employee employee=null;
List<Employee> list=null;
String sql="select id,username,pasw,realname,sex,birthday,edu,major,des,hiredate," +
"role from employees";
Class clazz=Employee.class;
try {
list = (List)super.findObjectByCondition(conn, sql, clazz);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
return list;
}
}
第四步、BaseDao.java
package cn.itcast.employees;
import *;
public abstract class BaseDao {
public void update(Connection conn, String sql, Object[] param) throws SQLException {
QueryRunner query=new QueryRunner();
query.update(conn, sql, param);
}
@SuppressWarnings("unchecked")
public Object findObjectById(Connection conn, String sql, Class clazz,
Object[] param) throws SQLException {
QueryRunner query=new QueryRunner();
return query.query(conn,sql,new BeanHandler(clazz),param);
}
public Object findObjectByCondition(Connection conn, String sql, Class clazz,
Object[] param) throws SQLException {
QueryRunner query=new QueryRunner();
return query.query(conn, sql, new BeanListHandler(clazz), param);
}
public Object findObjectByCondition(Connection conn, String sql, Class clazz) throws SQLException {
QueryRunner query=new QueryRunner();
return this.findObjectByCondition(conn, sql, clazz, null);
}
}