java jdbc 查询与更新实现

来源：互联网发布：淘宝助理打出尺寸不对编辑：程序博客网时间：2024/05/20 18:44
package jdbc.demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class Demo1 {    private final static String username = "root";    private final static String password = "tiger";    private final static String url = "jdbc:mysql://localhost/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC ";    Connection conn;// 连接引用    PreparedStatement ps;    public Connection getConnection() {        try {            Class.forName("com.mysql.cj.jdbc.Driver");// 加载jdbc驱动            try {                if (conn == null) {                    // 加载完后可以通过DriverManager获取数据库连接                    conn = DriverManager.getConnection(url, username, password);                    conn.setAutoCommit(false);// 关闭事务自动提交                }            } catch (SQLException e) {                System.out.println("连接不了数据库,或连接格式写错");                e.printStackTrace();                this.close();                return null;            }        } catch (ClassNotFoundException e) {            System.out.println("找不到驱动");            e.printStackTrace();            return null;        }        return conn;    }    public ResultSet find(Connection conn, String sql) {        ResultSet result = null;        try {            ps = conn.prepareStatement(sql);            result = ps.executeQuery();// 执行查询        } catch (SQLException e) {            // 查询一般没有事务要求            this.close();            e.printStackTrace();        }        return result;    }    public int updateInsertDelete(Connection conn, String sql) {        int i = 0;        try {            ps = conn.prepareStatement(sql);            i = ps.executeUpdate();// 执行表更新(插入,更新,删除都是表的更新操作)        } catch (SQLException e) {            try {                conn.rollback();// 回滚事务            } catch (SQLException e1) {                System.out.println("回滚事务失败");                e1.printStackTrace();            }            this.close();// 关闭资源            e.printStackTrace();        }        return i;    }    public void close() {        try {            if (!ps.isClosed()) {                ps.close();            }        } catch (SQLException e1) {            e1.printStackTrace();        }        try {            if (!conn.isClosed()) {                conn.close();            }        } catch (SQLException e1) {            e1.printStackTrace();        }    }    public static void main(String[] args) throws SQLException {        Demo1 jdbc = new Demo1();        Connection conn = jdbc.getConnection();        jdbc.updateInsertDelete(conn, "INSERT INTO test (username,create_time) VALUES ('user3',NOW());");        conn.commit();// 更新操作要提交事务才能生效        ResultSet result = jdbc.find(conn, "select id,username,update_time,create_time from test");        int maxCol = result.getMetaData().getColumnCount();        System.out.println("查询时指定列的数量,为*时则是表的列数maxCol: " + maxCol);        while (result.next()) {// 通过result.next()遍历结果            int row = result.getRow();            System.out.print("当前遍历的行数: " + row + " ");            // 第一列的列号为1,所以i最小为1            for (int i = 1; i <= maxCol; i++) {                System.out.print(result.getString(i) + " ");            }            System.out.println("");        }        jdbc.close();    }}// 查询时指定列的数量,为*时则是表的列数maxCol: 4// 当前遍历的行数: 1 1 user1 2017-05-29 10:19:39 2017-05-29 10:19:39// 当前遍历的行数: 2 2 user2 2017-05-29 10:20:15 2017-05-29 10:20:15// 当前遍历的行数: 3 5 user2 2017-05-29 10:22:12 2017-05-29 10:22:12// 当前遍历的行数: 4 12 user3 2017-05-29 10:42:10 2017-05-29 10:42:10// 当前遍历的行数: 5 13 user3 2017-05-29 10:42:12 2017-05-29 10:42:12// 当前遍历的行数: 6 14 user3 2017-05-29 10:42:17 2017-05-29 10:42:17// 当前遍历的行数: 7 15 user3 2017-05-29 10:43:34 2017-05-29 10:43:34// 当前遍历的行数: 8 16 user3 2017-05-29 10:43:42 2017-05-29 10:43:42// 当前遍历的行数: 9 17 user3 2017-05-29 10:44:33 2017-05-29 10:44:33// 当前遍历的行数: 10 18 user3 2017-05-29 10:44:42 2017-05-29 10:44:42// 当前遍历的行数: 11 19 user3 2017-05-29 10:56:58 2017-05-29 10:56:58// 当前遍历的行数: 12 20 user3 2017-05-29 11:28:13 2017-05-29 11:28:13// 当前遍历的行数: 13 21 user3 2017-05-29 11:29:02 2017-05-29 11:29:02/* * CREATE TABLE test ( id INT(11) NOT NULL AUTO_INCREMENT , username VARCHAR(50) * NOT NULL , update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE * CURRENT_TIMESTAMP, create_time DATETIME , PRIMARY KEY(id) )ENGINE=INNODB * DEFAULT CHARSET utf8mb4 COMMENT '测试表'; *  * INSERT INTO test (username,create_time) VALUES ('user1',NOW()); *  *  *  */
阅读全文
0 0