17_7_3 JDBC 简述 JDBC DB层和DBCp(链接池)层
2017-07-03 17:31 小歪1991 阅读(206) 评论(0) 编辑 收藏 举报步骤总结:
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")+"\tname:"
//+b.getString("name")+" age:"+b.getInt("age")+" address:"+b.getString("address"));
//}
}