Java Web基础整理-JDBC编程

1. 增删改查
2. 事务处理
3. 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','[e-mail]')";            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='[e-mail]' 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','[e-mail]')";            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','[e-mail]')";        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.Driverdburl=jdbc\:mysql\://localhost\:3306/jsp_dbuser=rootpassword=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();    }}

总结：整体流程（拿ppt随便画的）