Java Web基础整理-JDBC编程

最新推荐文章于 2024-11-18 08:41:09 发布

wuta0123

最新推荐文章于 2024-11-18 08:41:09 发布

阅读量425

点赞数

分类专栏： Java Web基础文章标签： java web java

本文链接：https://blog.csdn.net/wuta0123/article/details/50420726

版权

Java Web基础专栏收录该内容

1 篇文章 0 订阅

订阅专栏

增删改查
事务处理
DTO&DAO使用

先建两张表，分别插入两条数据供后面使用。
命令行固然炫酷，但Navicat更友好，当然workbench也可以啦。随意随意
实例所用的表和数据

1、增删改查

/**基本的增删改查正如代码中注释，就是那四步：
  *1、注册jdbc驱动；2、获取数据库连接；3、创建statement对象；4、调用executeUpdate()方法；
  */
public class JDBCTest {
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");//注册mysql的jdbc驱动程序
            conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","123456");//获取数据库连接

        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void insert(){
        Connection conn = getConnection();
        try {
            String sql = "insert into tbl_user(name,password,email)" +
                         "values('Tom','123456','tom@gmail.com')";
            Statement st = conn.createStatement();//创建statement对象
            int count = st.executeUpdate(sql);//调用statement对象的executeUpdate()方法执行sql语句
            System.out.println("向用户表中插入了"+ count + "条记录");
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void update(){
        Connection conn = getConnection();
        try {
            String sql = "update tbl_user SET email='tom@126.com' where name = 'TOM'"; 
            Statement st = conn.createStatement();
            int count = st.executeUpdate(sql);
            System.out.println("向用户表中更新了"+ count + "条记录");
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void delete(){
        Connection conn = getConnection();
        try {
            String sql = "delete from tbl_user where name = 'TOM'"; 
            Statement st = conn.createStatement();
            int count = st.executeUpdate(sql);
            System.out.println("向用户表中删除了"+ count + "条记录");
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        //insert();
        //update();
        delete();
    }   
}

2、事务处理

当涉及多表同时操作，可能会数据操作不完整而导致坑爹的后果，因此要引入事务处理以保证数据的一致性。
事务：一个操作序列，要么都执行要么都不执行，具有原子性、一致性、隔离性、持久性。
主要调用方法是：提交commit()、回滚rollback()

//错误的同时插入方式，导致只能插入部分数据，破坏了数据的完整性
public class TransactionTest {
    //获取数据库连接
    public static Connection getConnection(){
        Connection conn=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","123456");
        }catch(Exception e){
            e.printStackTrace();
        }
        return conn;
    }

    public static void insertUserData(){
        Connection conn = getConnection();

        try{
            String sql="insert into tbl_user(id, name, password, email)"+
                       "values(10,'Tom','123456','tom@gmail.com')";
            Statement st=conn.createStatement();
            int count=st.executeUpdate(sql);
            System.out.println("向用户表插入了" + count +"条记录");
            conn.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

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

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

}

//通过利用回滚进行事务管理的正确方式
public class TransactionTest {
    // 获取数据库连接
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/jsp_db", "root", "123456");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    //将异常抛出给调用方法
    public static void insertUserData(Connection conn) throws SQLException {
        String sql = "insert into tbl_user(id, name, password, email)"
                + "values(10,'Tom','123456','tom@gmail.com')";
        Statement st = conn.createStatement();
        int count = st.executeUpdate(sql);
        System.out.println("向用户表插入了" + count + "条记录");
    }

    public static void insertAddressData(Connection conn) throws SQLException { 
            String sql = "insert into tbl_address(id, city, country, user_id)"
                    + "values(1, 'shanghai', 'china', '10')";
            Statement st = conn.createStatement();
            int count = st.executeUpdate(sql);
            System.out.println("向地址表中插入了" + count + "条记录");
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);//禁止事务自动提交
        } catch (Exception e) {
            // TODO: handle exception
        }
        try {
            insertUserData(conn);
            insertAddressData(conn);

            conn.commit();//提交事务
        } catch (Exception e) {
            System.out.println("*****************catch exception********************");
            e.printStackTrace();
            try{
                conn.rollback();
                System.out.println("*************************rollback successful*****************************");
            } catch(Exception e2){
                e2.printStackTrace();
            }
        }finally{
            try {
                if(conn!=null){
                    conn.close();
                }
            } catch (Exception e3) {
                e3.printStackTrace();
            }
        }
    }

}

3、DTO&DAO使用

DTO(data transfer object)：封装数据传输对象，不包含业务逻辑

//新建dbconfig.properties属性文件，加入以下键值对
driver=com.mysql.jdbc.Driver
dburl=jdbc\:mysql\://localhost\:3306/jsp_db
user=root
password=123456
//新建连接工厂类
public class ConnectionFactory {
    //为属性文件中的键值对声明四个成员变量
    private static String driver;
    private static String dburl;
    private static String user;
    private static String password;
    //声明类对象，由于是单例模式直接定义成了final类型
    private static final ConnectionFactory factory = new ConnectionFactory();
    //声明存储连接的connection对象
    private Connection conn;
    //用java的静态代码块读取属性文件的配置信息，静态代码块用于初始化类，为类的属性赋值，只会执行一次
    static{
        Properties prop = new Properties();  //定义一个Properties类，继承自hashtable，存储键值对
        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(){

    }
    //用于获取connectionFactory实例，这里使用了单例模式，以保证在程序运行期间只有一个connectionFactory实例存在
    public static ConnectionFactory getInstance(){
        return factory;
    }

    public Connection makeConnection(){
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(dburl, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
}
//测试数据库连接是否成功
public class ConnectionFactoryTest {
    public static void main(String[] args) throws Exception{
        ConnectionFactory cf = ConnectionFactory.getInstance();

        Connection conn = cf.makeConnection();

        System.out.println(conn.getAutoCommit());
    }

}

DAO(data access object)：数据访问对象，用于封装数据访问

//创建超类
public abstract class IdEntity {
    protected Long id;

    public Long getId(){
        return id;
    }

    public void setId(Long id){
        this.id=id;
    }

}
//创建实体子类
package com.csdn.entity;

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

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

}
//声明接口，定义实现类的访问操作，约定行为
public interface UserDao {
    public void save(Connection conn, User user) throws SQLException;

    public void update(Connection conn, Long id, User user) throws SQLException;

    public void delete(Connection conn, User user) throws SQLException;

}
//实现接口
public class UserDaoImpl implements UserDao {
    // 保存
    @Override
    public void save(Connection conn, User user) throws SQLException {
        // preparedStatement用于执行参数化查询，？为占位符
        PreparedStatement ps = conn
                .prepareCall("insert into tbl_user(name,password,email) values (?,?,?)");
        // 索引由1开始
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        ps.execute();

    }

    // 更新
    @Override
    public void update(Connection conn, Long id, User user) throws SQLException {
        String updateSql = "update tbl_user set name = ?, password = ?, email = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(updateSql);
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        ps.setLong(4, id);

    }

    // 删除
    @Override
    public void delete(Connection conn, User user) throws SQLException {
        PreparedStatement ps = conn
                .prepareStatement("delete from tbl_user where id = ?");
        ps.setLong(1, user.getId());
        ps.execute();

    }

}