一、连接数据库
五次迭代优化:
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionTest {
//方式一:
@Test
public void testConnection1() throws SQLException {
Driver driver = new com.mysql.jdbc.Driver();
//jdbc:mysql:协议
//localhost:ip地址
//3306:默认MySQL的端口号
//test:数据库名
String url = "jdbc:mysql://localhost:3306/test";
//将用户名和密码封装在Properties中
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "123456");
Connection conn = driver.connect(url, info);
System.out.println(conn);
}
//方式二:对方式一的迭代
//不使用第三方创建Driver对象--反射,使程序具有更好的可移植性
@Test
public void testConnection2() throws Exception {
//1.获取Driver实现类对象:使用反射
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//2.提供要连接的数据库
String url = "jdbc:mysql://localhost:3306/test";
//3.将用户名和密码封装在Properties中
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "123456");
//4.获取连接
Connection conn = driver.connect(url, info);
System.out.println(conn);
}
//方式三:使用DriverManager替换Driver
@Test
public void testConnection3() throws Exception {
//1.获取Driver实现类对象:使用反射
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//2.提供三个连接基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
//1.注册驱动
DriverManager.registerDriver(driver);
//2.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
//方式四:优化驱动,省略驱动注册,已经封装过了
@Test
public void testConnection4() throws Exception {
//1.提供三个连接基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
//2.加载Driver
Class.forName("com.mysql.jdbc.Driver");
//Driver driver = (Driver) clazz.newInstance();
//注册驱动
//DriverManager.registerDriver(driver);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
//方式五:将数据库连接的四个基本信息放在配置文件中
//好处何在?
//1.实现了数据与代码的分离,实现了解耦
//2.如果需要修改配置文件信息,可以避免重新打包
@Test
public void getConnection5() throws Exception {
//1.读取配置文件信息
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driver");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
}
二、用JDBC实现增删改操作
import com.atguigu.connection.ConnectionTest;
import com.atguigu.util.JDBCUtils;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Properties;
/*
* 使用preparedstatement来代替statement,实现对数据表的增删改查操作
*
* 增删改 查
* 无返回 有返回
* */
public class UpdateTest {
@Test
public void testCommonUpdate(){
// String sql = "delete from customers where id = ?";
// update(sql,3);
String sql = "update test.order set order_name = ? where order_id = ?";
update(sql,"DD","2");
}
//通用的增删改操作
public void update(String sql,Object ...args){
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句,返回PreparedStatement的实例
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.资源的关闭
JDBCUtils.closeResource(conn, ps);
}
}
//修改customers记录
@Test
public void testUpdate(){
Connection conn = null;
PreparedStatement ps = null;
try {
//1.获取数据库的连接
conn = JDBCUtils.getConnection();
//2.预编译sql语句,返回PreparedStatement的实例
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
//3.填充占位符
ps.setObject(1,"莫扎");
ps.setObject(2,18);
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.资源的关闭
JDBCUtils.closeResource(conn, ps);
}
}
@Test
public void getConnection5(){
//1.读取配置文件信息
Connection conn = null;
PreparedStatement ps = null;
try {
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driver");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
//4.预编译sql语句,返回PreparedStatement的实例
String sql = "insert into customers(name,email,birth)values(?,?,?)";//?:占位符
ps = conn.prepareStatement(sql);
//5.填充占位符
ps.setString(1,"张三");
ps.setString(2,"zhangsan@gmail.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("1000-01-01");
ps.setDate(3, new Date(date.getTime()));
//6.执行sql
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//7.资源的关闭
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
三、针对于customers表的通用的查询操作
import java.sql.*;
/*
* 查询
*
* */
public class ForQuery {
@Test
public void testQFC(){
String sql = "select id,name,birth,email from customers where id = ?";
Customer customer = queryForCustomers(sql, 10);
System.out.println(customer);
sql = "select name,email from customers where name = ?";
Customer customer1 = queryForCustomers(sql, "周杰伦");
System.out.println(customer1);
}
/*
* 针对于customers表的通用的查询操作
*
* */
public Customer queryForCustomers(String sql,Object...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
if(rs.next()){
Customer cust = new Customer();
for(int i = 0;i <columnCount;i++){
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名
String columnName = rsmd.getColumnName(i + 1);
//给cust对象指定的columnName属性,赋值为columnValue:通过反射
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(cust, columnValue);
}
return cust;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeQResource(conn,ps,rs);
}
return null;
}
@Test
public void testQuery(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1,2);
//执行,并返回结果集
resultSet = ps.executeQuery();
//处理结果集
if(resultSet.next()){//判断结果集的下一条是否有数据,有则返回true,并指针下移;没有则返回false,指针不下移
//获取当前这条数据的各个字段值
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
//方式一:
System.out.println();
//方式二:
Object[] data = new Object[]{id,name,email,birth};
//方式三:
Customer customer = new Customer(id,name,email,birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeQResource(conn, ps, resultSet);
}
}
}
四、封装数据库的连接和资源的关闭操作
package com.atguigu.util;
import com.atguigu.connection.ConnectionTest;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
public static Connection getConnection() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String driverClass = pros.getProperty("driver");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
public static void closeResource(Connection conn, Statement ps){
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeQResource(Connection conn, Statement ps,ResultSet rs){
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}