DButils的更新与查询，利用C3P0链接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
	package star.july.util;
	
	import java.sql.Connection;
	import java.sql.SQLException;
	
	import javax.sql.DataSource;
	
	import com.mchange.v2.c3p0.ComboPooledDataSource;
	
	public class C3P0Util {
		private static ComboPooledDataSource ds = new ComboPooledDataSource();
		
		public static Connection getConnection(){
			try{
				Connection conn = ds.getConnection();
				return conn;
			}catch(SQLException e){
				e.printStackTrace();
				throw new RuntimeException();
			}
		}
		
		public static DataSource getDataSource(){
			return ds;
		}
	}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<c3p0-config>

	<!-- 默认配置 -->
	<default-config>
		<!-- 属性名称就是setter方法名称 -->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/day19</property>
		<property name="user">root</property>
		<property name="password">root</property>
		
		<property name="initialPoolSize">5</property>
		<property name="maxPoolSize">12</property>
		<property name="checkoutTimeout">3000</property>
	</default-config>
	
	<!-- 命名配置 -->
	<named-config name="day17">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/day17</property>
		<property name="user">root</property>
		<property name="password">root</property>
		
		<property name="initialPoolSize">5</property>
		<property name="maxPoolSize">15</property>
		<property name="checkoutTimeout">3000</property>
	</named-config>
	
</c3p0-config>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
package star.july.dbutil;

public class Student {
	private int id;
	private String name;
	private String gender;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", gender=" + gender
				+ "]";
	}
	
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package star.july.dbutil;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Test;

import star.july.util.C3P0Util;

//演示dbutils工具的更新操作
public class Demo1 {
	 
	//创建主程序
	QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
	
	QueryRunner qr2 = new QueryRunner();		//没有DataSource
	//没有事务操作
	@Test
	public void test1() throws Exception{
//	qr.update("insert into student(name,gender) values(?,?)", "狗蛋","女");
	qr.update("delete  from student where id = ?",4);
//		System.out.println("影响了"+count+"行");
	}
	
	//有事务操作
	@Test
	public void test2(){
		Connection conn = C3P0Util.getConnection();
		try{
			//获取Connection对象
			//1.开启事务
			conn.setAutoCommit(false);
			qr2.update(conn,"delete from student where id=?",2);
			
			//int i =100/0;
			
			qr2.update(conn,"delete from student where id=?",3);
			
			//提交事务
			conn.commit();
			
		}catch(Exception e){
			e.printStackTrace();
			//回滚事务
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
package star.july.dbutil;

import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import star.july.util.C3P0Util;

//查询类操作
public class Demo2 {
	QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
	
	//查询一条数据：BeanHandler，把结果集封装一个javabean对象
	//约定：表的字段名称和javabean的属性名称保持一致
	
	@Test
	public void test1() throws Exception{
		Student stu = qr.query("select * from student where id =?"	, new BeanHandler(Student.class),1);
		System.out.println(stu);
	}
	
	//查询多条数据：BeanListHandler:把结果集封装一个List集合（包含多个javabean对象）
	//约定：表的字段名称和javabean的属性名称保持一致！
	@Test
	public void test2() throws Exception{
		List<Student> list = qr.query("select* from student",new BeanListHandler(Student.class));
		for(Student stu:list){
			System.out.println(stu);
		}
	}
	
	//查询一条数据：ArrayHandler：把结果集封装一个对象数组
	@Test
	public void test3()throws Exception{
		Object[] array = qr.query("select * from student where id = ?", new ArrayHandler(),5);
		for(int i = 0 ; i <array.length;i++){
			System.out.print(array[i]);
		}
	}
	
	//查询多条数据：ArrayListHandler：把结果集封装一个List（包含多个对象数组）
	@Test
	public void test4() throws Exception{
		List<Object[]> list = qr.query("select * from student", new ArrayListHandler());
		for(Object[] obj:list){
			for(Object stu: obj){
				System.out.print(stu+"t");
			}
			System.out.println();
		}
	}
	
	//查询一条记录（且只有一个字段）：聚合查询 count（*） max() min() avg(): ScalarHandler:封装一个对象
	@Test
	public void test5() throws Exception{
		Long count = qr.query("select count(*) from student", new ScalarHandler());
		System.out.println(count);
	}
}