一、基本概念
- 数据持久化：把数据保存到可掉电式存储设备中以供以后使用，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以“固化”，而实现过程大多以关系型数据库完成。
- Java中数据库存取技术的分类：
JDBC直接访问数据库
JDO技术
第三方O/R工具，如Hibernate,ibatis等
- JDBC的定义：独立于DBMS，数据库通用的用来存取和操作数据的公共接口（API应用程序接口）。定义了用来访问数据库的标准java类库，使用这个类库可以方便的访问数据；
- JDBC驱动程序：各个厂商根据JDBC的规范制作的JDBC实现类的类库。
二、JDBC的使用
2.1、Driver接口
2.1.1、Eclipse的连接方式
Java.sql.Driver接口是所有JDBC驱动程序需要实现的接口，不同的数据库厂商提供不同的实现，由3驱动程序管理器类（java.sql.DriverManager）去调用这些Driver实现
Driver driver = new com.mysql.cj.jdbc.Driver();
Properties info = new Properties();
String url="jdbc:mysql://localhost:3306/test";//test为数据库名
info.put("uer","root");
info.put("password","root");
Connection connction = driver.connect(url,info);
System.out.println("connect successful");
2.1.2、IDEA的连接方式
//方法一正常连接
Driver driver=new com.mysql.jdbc.Driver();//第三方API
String url = "jdbc:mysql://localhost:3306/test1";
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","root");
Connection connect = driver.connect(url, info);
System.out.println(connect);
//方法二：反射
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver o = (Driver)aClass.newInstance();
String url="jdbc:mysql://localhost:3306/test1";
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","root");
Connection connect = o.connect(url,info);
System.out.println(connect);
//方法三：DriverManager
Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
Driver driver=(Driver)aClass.newInstance();
DriverManager.registerDriver(driver);
String url="jdbc:mysql://localhost:3306/test1";
String user="root";
String password="root";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
//方法四：省略写法
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/test1";
String user="root";
String password="root";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
//方法五：最终版，将数据库需要的信息卸载配置文件中，然后读取配置文件
//1.实现了数据与代码分离，实现了解耦
//2.如果需要修改配置文件信息，可以避免重新打包
InputStream resource = ConnectionTest.class.getClassLoader().getResourceAsStream("test0.properties");
Properties properties = new Properties();
properties.load(resource);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
2.2、CRUD操作
- 增加操作
public class ConnectionTest {
public static void main(String[] args) throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
//方法五：最终版，将数据库需要的信息卸载配置文件中，然后读取配置文件
try {
InputStream resource = ConnectionTest.class.getClassLoader().getResourceAsStream("test.properties");
System.out.println(resource);
Properties properties = new Properties();
properties.load(resource);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
//增删改操作
//向customers表中添加一条记录
String sql="insert into customers(name,email,birth)values(?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"dcd");
preparedStatement.setString(2,"nezha@gmail.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date parse = sdf.parse("1000-01-01");
preparedStatement.setDate(3, new java.sql.Date(parse.getTime()));
preparedStatement.execute();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
将连接操作和关闭连接操作进行封装
public class JDBCUtils {
/*
*@description: 获取数据库连接
*@author: dongcedian
*@date:2021/9/27
*/
public static Connection getConnection() throws Exception {
InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("test.properties");
System.out.println(resource);
Properties properties = new Properties();
properties.load(resource);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driver = properties.getProperty("driver");
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/*
*@description: 关闭操作
*@author: dongcedian
*@date:2021/9/27
*/
public void closeResource(Connection conn, Statement pre){
try {
pre.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- 修改操作
//使用到了上面包装好的Util类
public class ConnectionTest {
public static void main(String[] args){
Connection connection = null;
PreparedStatement ps = null;
try {
//1.建立连接
connection = JDBCUtils.getConnection();
//2.编写预编译sql语句
String sql="update customers set name=? where id=?";
ps = connection.prepareStatement(sql);
//3.填充占位符
ps.setObject(1,"莫扎特");
ps.setObject(2,18);
//4.执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.资源关闭
JDBCUtils.closeResource(connection,ps);
}
}
}
通用的增删改操作
public class ConnectionTest {
public static void main(String[] args){
String sql="update `order` set order_name=? where order_id=?";//order是关键字
update(sql,"DD",4);
}
public static void update(String sql,Object ...args){//占位符的个数，取决于可变形参数组的长度
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.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(connection,ps);
}
}
}
- 查询操作
- 创建一个package命名为bean,用于包装对象,结构如下图
- 查询需要读取，所以和其他几种不太相同
public class ConnectionTest {
@Test
public void fun(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet=null;
try {
connection = JDBCUtils.getConnection();
String sql="select id,name,email,birth from customers where id=?";
ps = connection.prepareStatement(sql);
ps.setObject(1,1);
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);
//将数据封装成一个对象
Customer customer = new Customer(id,name,email,birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,ps);
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
读取一条信息的通用写法
public class ConnectionTest {
@Test
public void fun(){
String sql="select id,name from customers where id=?";
Customer customer = queryCustomer(sql, 13);
System.out.println(customer);
}
//查找的通用函数
public Customer queryCustomer(String sql,Object ...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i< args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
Customer customer = new Customer();
if(resultSet.next()){
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnName = metaData.getColumnName(i+1);
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(customer,columnValue);
}
return customer;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,resultSet);
}
return null;
}
}
当需要别名的时候
public class ConnectionTest {
@Test
public void fun(){
String sql="select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id= ?";
System.out.println(queryOrder(sql,4));
}
//查找的通用函数
public order queryOrder(String sql,Object ...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i< args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if(resultSet.next()){
order order = new order();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
//获取列的列名，没有获取别名
// String columnName = metaData.getColumnName(i+1);
//针对数据库列名和程序名称不同的时候，应该选取程序中的名称作为别名，没有起别名就默认为列名
String columnLabel = metaData.getColumnLabel(i + 1);
Field field = order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order,columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn,ps,resultSet);
}
return null;
}
}
使用PreparedStatement实现对于不同类的通用的查询操作
public class ConnectionTest {
@Test
public void fun(){
String sql="select id,name,email from customers where id=?";
System.out.println(queryAll(Customer.class,sql,16));;
}
//查找的通用函数
public <T> T queryAll(Class<T> clazz,String sql,Object ...args){
Connection conn=null;
PreparedStatement ps=null;
ResultSet resultSet=null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if(resultSet.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
return t;
}
}catch (Exception e){
System.out.println(e.getMessage());
}finally {
JDBCUtils.closeResource(conn,ps,resultSet);
}
return null;
}
}
查询多行数据
public class ConnectionTest {
@Test
public void fun(){
String sql="select id,name,email from customers where id<?";
// for (Customer customer : getForList(Customer.class, sql, 12)) {
// System.out.println(customer);
// }
List<Customer> list = getForList(Customer.class, sql, 12);
list.forEach(System.out::println);
}
//查找的通用函数
public <T>List<T> getForList(Class<T> clazz,String sql,Object ...args){
Connection conn=null;
PreparedStatement ps=null;
ResultSet resultSet=null;
try{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;++i){
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while(resultSet.next()){
T t = clazz.newInstance();
for(int i=0;i<columnCount;++i){
Object columnValue = resultSet.getObject(i + 1);
String columnLabel = metaData.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
return list;
}catch (Exception e){
System.out.println(e.getMessage());
}finally {
JDBCUtils.closeResource(conn,ps,resultSet);
}
return null;
}
}
693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
