17_7_3 JDBC 简述 JDBC DB层和DBCp（链接池）层

步骤总结：

1.java project下面建立 lib文件夹，并放入.jar文件（百度 下载：输入：mysql java 驱动）
2.build path 生成 引入的 库文件

3.构造各层class

db.DBUtil代码:

package com.j1702.db;

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

public class DBUtil {

	private static final String URL = "jdbc:mysql://127.0.0.1:3306/jdbc_test" +
                                                     **"?useunicode&characterEncoding=UTF-8"**;//解决编码问题
                                                        +"useSSL=false"//解决链接数据库控制输出时有红色语句问题
	private static final String USER = "root";
	private static final String PASSWORD = "123456";
	private static Connection conn = null;

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			setConn(DriverManager.getConnection(URL, USER, PASSWORD));//注意：这一步，也可以不用写setConn（）方法，只写getConn（）方法
                         //conn=DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConn() {
		return conn;
	}

	public static void setConn(Connection conn) {
		DBUtil.conn = conn;
	}
}

DBCP层代码：

package com.j1702.dbcp;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DBCPUtil {

	private static Properties properties = new Properties();
	private static DataSource dataSource;
	// 加载DBCP配置文件
	static {
		try {
			FileInputStream is = new FileInputStream("config/dbcp.properties");
			properties.load(is);
			dataSource = BasicDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 从连接池中获取一个连接
	public static Connection getConnection() {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
}

model层中的方法层，model.dao.PersonDao代码：

	//增
	public static void add(SS s) throws SQLException{
		Connection conn=DBUtil.getConn();
		String sql="insert into ss(name,age,address) values(?,?,?) ";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, s.getName());
		ps.setInt(2, s.getAge());
		ps.setString(3, s.getAddress());
		ps.execute();
	}
	// 查询----将结果集装入对象中，
	public User queryUser(String name) throws Exception {
		Connection conn = DBUtil.geConnection();
		String sql = "select * from User where name=?";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, name);
		ResultSet res = ptmt.executeQuery();
		User u = new User();
		while (res.next()) {
			u.setName(res.getString("name"));
			u.setMoney(res.getInt("money"));
		}
		return u;
	}     
	// 查询----不将结果集装进去，而是返回ResultSet类型
	public User queryUser(String name) throws Exception {
		Connection conn = DBUtil.geConnection();
		String sql = "select * from User where name=?";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, name);
		return  res = ptmt.executeQuery();
		//while(res.next()){
		//	System.out.println("id:"+b.getInt("id")+"	name:"
		//+b.getString("name")+"  age:"+b.getInt("age")+"  address:"+b.getString("address"));
		//}
	}