JDBC

最新推荐文章于 2022-07-21 21:05:36 发布

原创最新推荐文章于 2022-07-21 21:05:36 发布 · 567 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#jdbc

jdbc 专栏收录该内容

2 篇文章

订阅专栏

补充

PreparedStatement 中使用mysql的like
请参考Using “like” wildcard in prepared statement

pstmt.setString(1, "%" + notes + "%");

PreparedStatement中使用日期
请参考Using setDate in PreparedStatement

ps.setDate(2, new java.sql.Date(endDate.getTime());

JDBC程序编写步骤

加载驱动
打开连接
执行查询
处理结果
清理环境

JDBC编程之数据查询

package com.wz.jdbc;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.Statement;

public class JDBCTest {
    public static void main(String[] args) {
        String sql = "SELECT * FROM tbl_user";
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {

            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "");
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                System.out.print(resultSet.getInt("id")+"    ");
                System.out.print(resultSet.getString("name")+"    ");
                System.out.print(resultSet.getString("password")+"    ");
                System.out.print(resultSet.getString("email")+"    ");
                System.out.println();
            }

        } catch (Exception e) {

            e.printStackTrace();

        }finally {
            try {
                resultSet.close();
            } catch (Exception e2) {
            }
            try {
                statement.close();
            } catch (Exception e2) {
            }
            try {
                connection.close();
            } catch (Exception e2) {
            }
        }

    }
}

console输出结果为：

1    xiaoming    123456    xiaoming@gmail.com    
2    xiaozhang    123456    xiaozhang@gmail.com

JDBC编程之数据更新

package com.wz.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class JDBCTest {

    //获取connection对象
    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    //插入方法
    public static void insert() {
        Connection connection = getConnection();
        try {

            String sql = "INSERT INTO tbl_user(name, password, email)" +
                                "VALUES('Tom', '123456', 'tom@gmail.com')";
            Statement statement = connection.createStatement();
            int count  = statement.executeUpdate(sql);
            System.out.println("向用户表中插入了 "+ count + " 条记录");
            connection.close();

        } catch (Exception e) {

            e.printStackTrace();

        }
    }

    //更新方法
    public static void update() {
        Connection connection = getConnection();
        try {

            String sql = "UPDATE tbl_user SET email = 'tom@126.com' WHERE name = 'Tom'";
            Statement statement = connection.createStatement();
            int count  = statement.executeUpdate(sql);
            System.out.println("向用户表中更新了  "+ count + " 条记录");
            connection.close();

        } catch (Exception e) {

            e.printStackTrace();

        }
    }

    //删除记录
    public static void delete() {
        Connection connection = getConnection();
        try {

            String sql = "DELETE FROM tbl_user WHERE name = 'Tom'";
            Statement statement = connection.createStatement();
            int count  = statement.executeUpdate(sql);
            System.out.println("向用户表中删除了  "+ count + " 条记录");
            connection.close();

        } catch (Exception e) {

            e.printStackTrace();

        }
    }

    public static void main(String[] args) {
        //insert();
        //update();
        delete();
    }
}

JDBC编程之事务处理

事务的四个特征

原子性
一致性
隔离性
持久性

事务的语句

开始事务：BEGIN TRANSACTION
提交事务：COMMIT TRANSACTION
回滚事务：ROLLBACK TRANSACTION

package com.wz.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class TransactionTest {

    public static Connection getConnection() {
        Connection connection = null;
        try {

            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "");

        } catch (Exception e) {

            e.printStackTrace();

        }
        return connection;
    }

    //插入用户数据
    public static void insertUserData() {
        Connection connection = getConnection();
        try {
            String sql = "INSERT INTO tbl_user(id, name, password, email)" + 
                                "VALUES(10, 'Tom', '123456', 'tom@gmail.com')";
            Statement statement = connection.createStatement();
            int count = statement.executeUpdate(sql);
            System.out.println("向用户表插入了 "+ count + " 条记录");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //向地址表中插入数据
    public static void insertAddressData() {
        Connection connection = getConnection();
        try {
            String sql = "INSERT INTO tbl_address(id, city, country, user_id)" + 
                                "VALUES(1, 'shanghai', 'china', '10')";
            Statement statement = connection.createStatement();
            int count = statement.executeUpdate(sql);
            System.out.println("向地址表插入了 "+ count + " 条记录");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        insertUserData();
        insertAddressData();
    }

}

在console中会输出错误信息：

向用户表插入了 1 条记录
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'

在用户表中插入了数据，但是地址表中，却提示主键重复。这是一个严重的问题，只有部分的数据被插入。造成了数据完整性的问题。

修改代码，事务回滚

// 插入用户数据
    public static void insertUserData(Connection connection) throws SQLException {
        String sql = "INSERT INTO tbl_user(id, name, password, email)" + "VALUES(10, 'Tom', '123456', 'tom@gmail.com')";
        Statement statement = connection.createStatement();
        int count = statement.executeUpdate(sql);
        System.out.println("向用户表插入了 " + count + " 条记录");
    }

    // 向地址表中插入数据
    public static void insertAddressData(Connection connection) throws SQLException {
        String sql = "INSERT INTO tbl_address(id, city, country, user_id)" + "VALUES(1, 'shanghai', 'china', '10')";
        Statement statement = connection.createStatement();
        int count = statement.executeUpdate(sql);
        System.out.println("向地址表插入了 " + count + " 条记录");
    }

    public static void main(String[] args) {
        Connection connection = null;
        try {

            connection = getConnection();
            connection.setAutoCommit(false);//禁止自动提交

            insertUserData(connection);
            insertAddressData(connection);

            connection.commit();
        } catch (SQLException e) {
            System.out.println("=============捕获到SQL异常================");
            e.printStackTrace();
            try {
                connection.rollback();//回滚事务
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }finally {
            //资源清理
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }

这时控制台输出：
向用户表插入了 1 条记录

=============捕获到SQL异常================
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'

查看用户表和地址表，会发现，并没有插入数据。

JDBC程序优化

在上述代码中，所有的数据库信息都是硬编码到Java代码中的，这是不可取的方式。把这些信息提取出来，放置到属性文件中。
新建一个.properties文件
这里写图片描述

driver=com.mysql.jdbc.Driver
dburl=jdbc:mysql://localhost:3306/jsp_db
user=root
password=

创建一个连接工厂类ConnectionFactory

package com.wz.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

//连接工厂类
public class ConnectionFactory {
    private static String driver;
    private static String dburl;
    private static String user;
    private static String password;

    private static final ConnectionFactory factory = new ConnectionFactory();

    private Connection connection;//数据库连接

    //静态代码块 只会执行一次
    //数据库信息读取代码
    static{
        Properties prop = new Properties();
        try {
            InputStream in = ConnectionFactory.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            prop.load(in);
        } catch (Exception e) {
            System.out.println("==============配置文件读取错误============");
        }
        driver = prop.getProperty("driver");
        dburl = prop.getProperty("dburl");
        user = prop.getProperty("user");
        password = prop.getProperty("password");

    }

    private ConnectionFactory(){

    }

    public static ConnectionFactory getInstance() {
        return factory;
    }

    public Connection makeConnection() {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(dburl, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

}

在创建一个测试类ConnectionFactoryTest，测试下：

package com.wz.jdbc;

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

import com.wz.util.ConnectionFactory;

public class ConnectionFactoryTest {

    public static void main(String[] args) throws SQLException {

        ConnectionFactory cf = ConnectionFactory.getInstance();//获取ConnectionFactory的一个实例
        Connection connection = cf.makeConnection();//获取一个数据库连接
        System.out.println(connection.getAutoCommit());//打印connection的一个属性
    }

}

console输出为：

true

进一步优化，创建DTO类

DAO叫数据访问对象
DTO是数据传输对象
DAO通常是将非对象数据（如关系数据库中的数据）以对象的方式操纵。
DTO通常用于不同层（UI层、服务层或者域模型层）直接的数据传输，以隔离不同层，降低层间耦合

首先创建IdEntity类，如下：

package com.wz.entity;

public abstract class IdEntity {
    protected Long id;
    public Long getId(){
        return id;
    }
    public void setId(Long id){
        this.id = id;
    }
}

再创建用户表对应的类，User继承自IdEntity：

package com.wz.entity;

public class User extends IdEntity {
    private String name;
    private String pasword;
    private String email;

    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPasword() {
        return pasword;
    }
    public void setPasword(String pasword) {
        this.pasword = pasword;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User [name=" + name + ", pasword=" + pasword + ", email=" + email + ", id=" + id + "]";
    }


}

再创建地址表对应的类，Address继承自IdEntity

package com.wz.entity;

public class Address extends IdEntity {
    private String city;
    private String country;
    private Long userId;

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getCountry() {
        return country;
    }

    public void setCountry(String country) {
        this.country = country;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    @Override
    public String toString() {
        return "Address [city=" + city + ", country=" + country + ", userId=" + userId + ", id=" + id + "]";
    }

}

接下来，再创建DAO对象，用来对数据库访问。把数据库中的表转化为DTO类。
创建一个接口类UserDao

package com.wz.dao;

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

import com.wz.entity.User;

public interface UserDao {
    //保存用户信息
    public void save(Connection connection, User user) throws SQLException;
    //更新用户信息
    public void update(Connection connection, Long id,  User user) throws SQLException;
    //删除用户信息
    public void delete(Connection connection, User user) throws SQLException;
}

再创建一个UserDaoImpl类，实现UserDao：

package com.wz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.wz.dao.UserDao;
import com.wz.entity.User;

public class UserDaoImpl implements UserDao {

    /**
     * 保存用户信息
     */
    @Override
    public void save(Connection connection, User user) throws SQLException {

        PreparedStatement ps = connection.
                prepareCall("INSERT INTO tbl_user(name, password, email) VALUES (?,?,?)");
        ps.setString(1, user.getName());
        ps.setString(2, user.getPasword());
        ps.setString(3, user.getEmail());
        ps.execute();
    }

    /**
     * 根据用户指定的ID更新用户信息
     */
    @Override
    public void update(Connection connection,Long id, User user) throws SQLException {
        String updateSql = "UPDATE tbl_user SET name = ?, password = ?, email = ? WHERE id = ?";

        PreparedStatement ps = connection.prepareStatement(updateSql);
        ps.setString(1, user.getName());
        ps.setString(2, user.getPasword());
        ps.setString(3, user.getEmail());
        ps.setLong(4, id);

    }

    /**
     * 删除指定的用户信息
     */
    @Override
    public void delete(Connection connection, User user) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("DELETE FROM tbl_user WHERE id = ?");
        ps.setLong(1, user.getId());
        ps.execute();
    }

}

新建一个测试类，来验证一下，如下：

package com.wz.test;

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

import com.wz.dao.UserDao;
import com.wz.dao.impl.UserDaoImpl;
import com.wz.entity.User;
import com.wz.util.ConnectionFactory;

public class UserDaoTest {

    public static void main(String[] args) {

        Connection connection = null;
        try {
            connection = ConnectionFactory.getInstance().makeConnection();
            connection.setAutoCommit(false);//关闭事务的自动提交

            UserDao userDao = new UserDaoImpl();
            User tom = new User();
            tom.setName("Tom");
            tom.setPasword("123456");
            tom.setEmail("tom@gmail.com");

            userDao.save(connection, tom);
            connection.commit();

        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (Exception e2) {
                e.printStackTrace();
            }
        }
    }

}