练习题——使用DUBTils工具类和C3P0数据源完成对数据库的操作

步骤

0、前话

表字段

1、新建文件夹导入相应jar包

c3p0、mchange是C3P0数据源所需
commons、mysql是DUBTils所需

2、对jar包分别构造路径（build path）

3、如果要使用JUnit（相当于一个个主函数）

选中项目，鼠标右键

设置完成后

4、建立JavaBean（一般放在entity包下面）

因为要使用DUBTils工具类，映射JavaBean，所以JavaBean中的成员对象（变量）必须与数据库表中的字段名（列名）相同

package com.homework.entity;

public class Student {
	//手写部分
	private int sid;
	private String sname;
	private int sage;
	private String ssex;
	private String semail;
	//alt + shift + s 选择对应的自动生成方法
	public Student() {
		super();
	}
	public Student(int sid, String sname, int sage, String ssex, String semail) {
		super();
		this.sid = sid;
		this.sname = sname;
		this.sage = sage;
		this.ssex = ssex;
		this.semail = semail;
	}
	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public int getSage() {
		return sage;
	}
	public void setSage(int sage) {
		this.sage = sage;
	}
	public String getSsex() {
		return ssex;
	}
	public void setSsex(String ssex) {
		this.ssex = ssex;
	}
	public String getSemail() {
		return semail;
	}
	public void setSemail(String semail) {
		this.semail = semail;
	}
	
}

5、测试，使用

package com.homework.day09;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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

import com.homework.entity.Student;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test_DUBTils_C3P0 {
	//使用DUBTils转载C3P0数据源中的连接对象
	ComboPooledDataSource ds = new ComboPooledDataSource();
	QueryRunner qr = new QueryRunner(ds);
	
//1）插入至少10条不同的学生信息到表中
	@Test
	public void insertTen() {
		int rows = 0;
		String sql = "INSERT INTO student(sname,sage,ssex,semail)\r\n" + 
				"	VALUES('张三','17','男','110@qq.com'),\r\n" + 
				"	('张三','17','男','110@qq.com'),\r\n" + 
				"	('李四','17','女','110@qq.com'),\r\n" + 
				"	('王五','17','男','110@qq.com'),\r\n" + 
				"	('赵六','17','男','110@qq.com'),\r\n" + 
				"	('田七','17','男','110@qq.com'),\r\n" + 
				"	('徐八','17','女','120@qq.com'),\r\n" + 
				"	('韩九','17','女','130@qq.com'),\r\n" + 
				"	('潘十','17','女','140@qq.com'),\r\n" + 
				"	('蒋十一','17','女','160@qq.com'),\r\n" + 
				"	('唐十二','17','男','190@qq.com'),\r\n" + 
				"	('萧十三','17','男','100@qq.com')";
		try {
			rows = qr.update(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(rows + "行受影响");
	}
	
//2）查询出班上所有的女生的信息，并打印到控制台
	@Test
	public void selectWoman() {
		List<Student> list = new ArrayList<Student>();
		String sql = "SELECT * FROM student WHERE ssex = '女'";
		try {
			list = qr.query(sql, new BeanListHandler<Student>(Student.class));
			for (Student s : list) {
				System.out.println(s.getSid() + " " + s.getSname() + " " + s.getSage() + " " + s.getSsex() + " " + s.getSemail());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
//3）查询出班上年龄大于21岁的男同学的姓名，并打印到控制台
	@Test
	public void selectGtMan() {
		List<Student> list = new ArrayList<Student>();
		String sql = "SELECT * FROM student WHERE sage >21 AND ssex = '男' ";
		try {
			list = qr.query(sql, new BeanListHandler<Student>(Student.class));
			Iterator<Student> it = list.iterator();
			while(it.hasNext()) {
				Student s = it.next();
				System.out.println(s.getSid() + " " + s.getSname() + " " + s.getSage() + " " + s.getSsex() + " " + s.getSemail());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
//4）根据学号删除某同学
	@Test
	public void deleteSid() {
		Student stu = new Student(16,null, 0, null, null);
		int i = 0;
		String sql = "DELETE FROM student WHERE sid = ?";
		try {
			i = qr.update(sql, stu.getSid());
			System.out.println(i);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//5）对某同学的信息进行修改
	@Test
	public void updateSid() {
		Student stu = new Student(0,"王五", 0, null, "000@qq.com");
		int i = 0;
		String sql = "UPDATE student SET semail = ? WHERE sname = ? ";
		try {
			i = qr.update(sql,stu.getSemail(),stu.getSname());
			System.out.println(i);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

PS
仅学习记录使用，若存在错误，还望指正

相关内容详解，见——👉Java代码操作数据库(下)👈