<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/students</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="acquireIncrement">3</property>
<property name="initialPoolSize">5</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">8</property>
</default-config>
</c3p0-config>
package cn.jdbc;
import java.sql.Connection;
import java.sql.SQLException;
import org.junit.Test;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JDBC {
private static ComboPooledDataSource dataSource=new ComboPooledDataSource();
//配置文件的默认配置,要求你必须给出c3p0-config.xml!!!
/**
* @throws SQLException
* 使用连接池返回一个连接对象
* @return
*/
public static Connection getConnection() throws SQLException{
return dataSource.getConnection();
}
/**
* 返回连接池对象
* @return
*/
public static ComboPooledDataSource getDataSource(){
return dataSource;
}
@Test
public void test()throws SQLException
{
if(JDBC.getConnection()!=null)
{
System.out.println("数据库连接成功");
}
else
System.out.println("数据库连接失败");
}
}
package cn.jdbc;
public class Stu {
private int id;
private String sname;
private int age;
private String gender;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Stu() {
super();
// TODO Auto-generated constructor stub
}
public Stu(int id, String sname, int age, String gender) {
super();
this.id = id;
this.sname = sname;
this.age = age;
this.gender = gender;
}
@Override
public String toString() {
return "Stu [id=" + id + ", sname=" + sname + ", age=" + age
+ ", gender=" + gender + "]";
}
}
package cn.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.DataSources;
public class QR<T> {
//private DataSources dataSource;
private static JDBC t1=new JDBC();
/*public QR(Connection connection)
{
this.dataSource=connection;
}*/
public QR()
{
super();
}
/**
* 做insert ,updata, delete
* @throws SQLException
*
*/
public int updata(String sql,Object... params) throws SQLException{
Connection con=null;
PreparedStatement pstmt=null;
con=t1.getConnection();
pstmt=con.prepareStatement(sql);
initparams(pstmt,params);//设置参数
return pstmt.executeUpdate();//执行
}
//给参数赋值
private void initparams(PreparedStatement pstmt,Object... params) throws SQLException{
for(int i=0;i<params.length;i++){
pstmt.setObject(i+1, params[i]);
}
}
public T query(String sql,RsHandler<T> rh,Object... params) throws SQLException{
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
con=JDBC.getConnection();
pstmt=con.prepareStatement(sql);
initparams(pstmt, params);
rs=pstmt.executeQuery();
return rh.handle(rs);
}
}
interface RsHandler<T>{
public T handle(ResultSet rs) throws SQLException;
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.management.RuntimeErrorException;
import org.junit.Test;
public class Demo1 {
@Test
public void addStu(Stu stu){
Connection con=null;
PreparedStatement pstmt =null;
try {
con=JDBC.getConnection();
String sql="insert into stu values(?,?,?,?)";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, stu.getId());
pstmt.setString(2, stu.getSname());
pstmt.setInt(3, stu.getAge());
pstmt.setString(4, stu.getGender());
pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally
{
if(pstmt!=null){
try {
pstmt.close();
if(con!=null){
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
@Test
public void deleteStu(int sid){
Connection con=null;
PreparedStatement pstmt=null;
try {
con=JDBC.getConnection();
String sql="delete from stu where sid=?";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, sid);
pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
if(pstmt!=null)
{
try {
pstmt.close();
if(con!=null)
{
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
@Test
public Stu load(int sid){
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBC.getConnection();
String sql="select *from stu where sid=?";
pstmt=con.prepareStatement(sql);
pstmt.setInt(1, sid);
rs=pstmt.executeQuery();
if(!rs.next())
return null;
Stu stu=new Stu();
stu.setAge(rs.getInt("age"));
stu.setGender(rs.getString("gender"));
stu.setId(rs.getInt("sid"));
stu.setSname(rs.getString("sname"));
return stu;
} catch (Exception e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
finally{
if(rs!=null)
{
try {
rs.close();
if(pstmt!=null)
{
pstmt.close();
}
if(con!=null)
{
con.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
package cn.jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class Demo2 {
@Test
public void fun1() throws SQLException
{
//Stu s=new Stu(1002,"zhansan",99,"male");
Demo1 t1=new Demo1();
//addStu(s);
Stu s=t1.load(1001);
System.out.println(s);
}
public void addStu(Stu stu) throws SQLException{
QR qr=new QR();
String sql="insert into stu values(?,?,?,?)";//给出sql模板
Object[] params={ stu.getId(),stu.getSname(),stu.getAge(),stu.getGender()};
qr.updata(sql, params);
}
public Stu load(int sid) throws SQLException{
QR qr=new QR();
String sql="select *from stu where sid=?";
Object[] params={sid};
RsHandler <Stu> rh=new RsHandler<Stu>(){
public Stu handle(ResultSet rs) throws SQLException{
if(!rs.next())return null;
Stu stu=new Stu();
stu.setId(rs.getInt("sid"));
stu.setSname(rs.getString("sname"));
stu.setAge(rs.getInt("age"));
stu.setGender(rs.getString("gender"));
return stu;
}
};
return (Stu) qr.query(sql, rh, params);
}
}
package cn.jdbc;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
public class Demo3 {
@Test
public void fun1() throws SQLException{
QueryRunner qr =new QueryRunner(JDBC.getDataSource());
String sql="insert into stu values(?,?,?,?)";
Object []params={1005,"lisi",88,"female"};
qr.update(sql,params);
}
@Test
public void fun2() throws SQLException{
QueryRunner qr=new QueryRunner(JDBC.getDataSource());
String sql="select *from stu where sid=?";
Object[] params={1002};
Stu stu=qr.query(sql, new BeanHandler<Stu>(Stu.class),params);
System.out.println(stu);
}
@Test
/**
* BeanListHandler的应用,它是多行处理器
* 每行对应一个Stu对象
* @throws SQLException
*/
public void fun3() throws SQLException{
QueryRunner qr=new QueryRunner(JDBC.getDataSource());
String sql="select *from stu";
List<Stu> stulist=qr.query(sql, new BeanListHandler<Stu>(Stu.class));
System.out.println(stulist);
}
@Test
/**
* MapHandler的应用,它是单行处理器,即把一行转换成一个Map对象
* @throws SQLException
*/
public void fun4() throws SQLException{
QueryRunner qr=new QueryRunner(JDBC.getDataSource());
String sql="select *from stu where sid=?";
Object [] params={1001};
Map map=qr.query(sql, new MapHandler(),params);
System.out.println(map);
}
@Test
/**
* MapListHandler,它是多行处理器,把每行都转换成一个Map,即List<Map>
* @throws SQLException
*/
public void fun5() throws SQLException{
QueryRunner qr=new QueryRunner(JDBC.getDataSource());
String sql="select *from stu";
List<Map<String , Object>>maplist =qr.query(sql, new MapListHandler());
System.out.println(maplist);
}
@Test
/**
* ScalarHandler 它是单行单列时使用,最为合适
* @throws SQLException
*/
public void fun6() throws SQLException
{
QueryRunner qr=new QueryRunner(JDBC.getDataSource());
String sql="select count(*) from stu";
Number cnt=(Number) qr.query(sql, new ScalarHandler());
long c=cnt.longValue();
System.out.println(c);
}
}