properties的配置
连接数据库的五种方法
增删改的通用操作
查询通用操作
properties的配置:
user=root
password=1234
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
连接数据库的五种方法:
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
public class ConnectionTest {
private Connection connection;
//方式一
@Test
public void testConnection1() throws SQLException {
Driver driver = new com.mysql.jdbc.Driver();
String url = "jdbc:mysql://localhost:3306/test";
//将用户名和密码封装在Properties内
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password","1234");
Connection conn = driver.connect(url, info);
System.out.println(conn);
}
//方式二:方式一的迭代(在以下程序中不出现第三方api,有更好的可移植性)
@Test
public void testConnection2() throws Exception{
//获取Driver实现类对象
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//提供要连接的数据库
String url = "jdbc:mysql://localhost:3306/test";
//提供用户密码
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password","1234");
//获取连接
Connection conn = driver.connect(url, info);
System.out.println(conn);
}
//方式三:使用DriverManager替换Driver
@Test
public void testConnection3() throws Exception {
//获取Driver实现类的对象
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//提供另外三个连接的基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "1234";
//注册驱动
DriverManager.registerDriver(driver);
//获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
//方式四:可以只加载驱动,不显示注册驱动
@Test
public void testConnection4() throws Exception {
//提供三个连接的基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "1234";
//加载Driver(可省略)
Class.forName("com.mysql.jdbc.Driver");
//获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
//方式五:将数据库连接需要的4个基本信息声明在配置文件中,通过读取配置文件的方式连接数据库
/*
* 实现数据与代码的分离,实现了解耦
*
*/
@Test
public void getConnection5() throws Exception {
//读取配置文件中的4个基本信息
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//加载驱动
Class.forName(driverClass);
//获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
}
增删改的通用操作:
@Test
public void testCommonUpdate() {
String sql = "delete from customers where id = ?";
update(sql,3);
}
//通用的增删改
public void update(String sql,Object...args){
Connection conn = null;
PreparedStatement ps = null;
try {
//连接数据库
conn = JDBCUtils.getConnection();
//预编译数据库
ps = conn.prepareStatement(sql);
//填充占位符
for(int i = 0;i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(conn, ps);
}
}
查询通用操作:
@Test
public void testGetInstance() {
String sql = "select id,name,email from customers where id = ?";
Customer customer = getInstance(Customer.class,sql,12);
System.out.println(customer);
String sql1 = "select order_id orderId,order_name orderName from `order` where order_id = ?";
Order order = getInstance(Order.class,sql1,1);
System.out.println(order);
}
/*
* 针对不同的表的通用的查询操作,返回表中的一条数据
*/
public <T> T getInstance(Class<T> clazz,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();
//通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
T t = clazz.newInstance();
//处理结果集一行数据中的每一列
for(int i = 0; i < columnCount; i++) {
//获取列值
Object columValue = rs.getObject(i + 1);
//获取每个列的列名
String columnLabel = rsmd.getColumnLabel(i + 1);
//给t对象指定的属性赋值为value,通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}