jdbc 总结 + 完整代码

记： 平时用惯了ssm，简化了数据库操作，现在回顾下：

1、什么是JDBC
 JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，是用Java语言编写的类和接口组成的，可以为多种关系型数据库提供统一访问的接口。JDBC提供了一种基准，说白了，也就是sun公司为各大数据库厂商的关系型数据库连接java所制定的规范，因此他们只需要实现JDBC的接口规范即可，而具体的实现是由各大数据库厂商去实现的，由于每种数据库的独特性，Java是无法控制的，所以JDBC是一种典型的桥接模式。我们据此也可以构建更高级的工具和接口，比如封装常用的CRUD工具类，使数据库开发人员能够更快速、高效、简便的开发数据库应用程序。
 2、使用JDBC进行CRUD操作

 准备内容
 mysql.jar 包下载https://dev.mysql.com/downloads/file/?id=472651 zip
 步骤： 
 1、加载驱动和注册数据库信息。 
 2、打开Connection,获取PreparedStatement对象。
 3、通过PreparedStatement执行SQL，返回结果到ResultSet对象。
 4、使用ResultSet读取数据，然后通过代码转换为具体的POJO对象。
 5、关闭数据库相关资源，先开的后关，后开的先关。

  

public class JdbcTest {private Logger logger = LoggerFactory.getLogger(getClass());private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";private static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";private static final String USERNAME = "root";private static final String PASSWORD = "root";private Connection getConnection() {Connection conn = null;/* * String url = "jdbc:mysql://localhost:3306/test?" + * "user=root&password=root&useUnicode=true&characterEncoding=UTF8"; */try {// 之所以要使用下面这条语句，是因为要使用MySQL的驱动，所以我们要把它驱动起来，// 可以通过Class.forName把它加载进去，也可以通过初始化来驱动起来，下面三种形式都可以// 使用Class.forName("com.mysql.jdbc.Driver")就是为了向DriverManager注册自己// Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动// or:// com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();// or：// new com.mysql.jdbc.Driver();// 一个Connection代表一个数据库连接// conn = DriverManager.getConnection(url);Class.forName(JDBC_DRIVER);System.out.println("成功加载MySQL驱动程序");conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);} catch (Exception e) {// TODO: handle exceptionlogger.info("Class={JdbcTest.class.getName()} not found",JdbcTest.class.getName(), e);}return conn;}/** * statement */public int StatementSave() {Connection conn = getConnection();int row = 0;String sql = "insert into tb_user (username,password,name,sex,email,tel) values('pdd','123456','qewr','1','[e-mail]','12345678901')";Statement st = null;try {st = conn.createStatement();row = st.executeUpdate(sql);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();logger.info("Bad SQL Grammer", e);} finally {close(null, st, conn);}return row;}/** *  *PreparedStatement */public int PreparedStatementSave(User user) {Connection conn = getConnection();int row = 0;// 5个问号(占位符)代表5个字段预先要保留的值String sql = "insert into tb_user (username,password,name,sex,email,tel) values(?,?,?,?,?,?)";PreparedStatement ps = null;try {/* * 使用PreparedStatement的优点： 　　　　 * 1、具有预编译功能，相同的SQL语句只需要编译一次，提高执行效率。 * 2、可以防止SQL语句注入，提高安全性 　　　　 */// 使用PreparedStatement对象里来构建并执行SQL语句//Statement.RETURN_GENERATED_KEYS   返回当前保存内容编号ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);// 通过PreparedStatement对象里的set方法设置要插入的值ps.setString(1, user.getUsername());ps.setString(2, user.getPassword());ps.setString(3, user.getName());ps.setString(4, user.getSex());ps.setString(5, user.getEmail());ps.setString(6, user.getTel());// 返回影响行数row = ps.executeUpdate();// 返回主键ResultSet rs = ps.getGeneratedKeys();if (rs.next()) {Long id = rs.getLong(1);System.out.println("数据主键：" + id);}} catch (SQLException e) {logger.info("Bad SQL Grammer", e);} finally {close(null, ps, conn);}return row;}

/** *  *批量导入 */public int PLpsSave() {Connection conn = getConnection();int row = 0;// 5个问号(占位符)代表5个字段预先要保留的值String sql = "insert into tb_user (username,password,name,sex,email,tel) values(?,?,?,?,?,?)";PreparedStatement ps = null;try {/* * 使用PreparedStatement的优点： 　　　　 * 1、具有预编译功能，相同的SQL语句只需要编译一次，提高执行效率。 * 2、可以防止SQL语句注入，提高安全性 　　　　 */// 使用PreparedStatement对象里来构建并执行SQL语句//Statement.RETURN_GENERATED_KEYS   返回当前保存内容编号ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);List<User> ulist=new ArrayList<User>();User user2=new User();user2.setName("pdd13");ulist.add(user2);User user1=new User();user1.setName("pdd23");ulist.add(user1);  for (int i = 0; i <ulist.size() ; i++) {    ps.setString(1, ulist.get(i).getUsername());ps.setString(2, ulist.get(i).getPassword());ps.setString(3, ulist.get(i).getName());ps.setString(4, ulist.get(i).getSex());ps.setString(5, ulist.get(i).getEmail());ps.setString(6, ulist.get(i).getTel());    ps.addBatch();            }  ps.executeBatch();} catch (SQLException e) {logger.info("Bad SQL Grammer", e);} finally {close(null, ps, conn);}return row;}/** *  * @description: 修改用户信息 * @param user * @return */public int update(User user) {Connection conn = getConnection();int row = 0;String sql = "update tb_user set password=? where username=?";PreparedStatement ps = null;try {ps = conn.prepareStatement(sql);ps.setString(1, user.getPassword());ps.setString(2, user.getUsername());row = ps.executeUpdate();} catch (SQLException e) {logger.info("Bad SQL Grammer", e);} finally {close(null, ps, conn);}return row;}/** *  * @description: 根据id删除用户 * @param id * @return */public int delete(Long id) {Connection conn = getConnection();int row = 0;String sql = "delete from tb_user where id='" + id + "'";PreparedStatement ps = null;try {ps = conn.prepareStatement(sql);row = ps.executeUpdate();} catch (SQLException e) {logger.info("Bad SQL Grammer", e);} finally {close(null, ps, conn);}return row;}/** *  * @description: 根据id查询用户信息/查询所有数据 * @param id * @return */public List<User> getAll(Long id) {Connection conn = getConnection();//String sql = "select * from tb_user";String sql = "select * from tb_user where id=?";PreparedStatement ps = null;ResultSet rs = null;List<User> list=new ArrayList<User>();try {ps = conn.prepareStatement(sql);ps.setLong(1, id); // 通过PreparedStatement执行Sql,返回结果到ResultSet对象 　　　　rs = ps.executeQuery();// 遍历结果集 　　   　while (rs.next()) {Long userId = rs.getLong(1);   //两种形式都可以不过数字那种取值形式效率更高    Long userId = rs.getLong("id");String username = rs.getString("username");String password = rs.getString("password");String name = rs.getString("name");String sex = rs.getString("sex");String email = rs.getString("email");String tel = rs.getString("tel");User user = new User();user.setId(userId);user.setUsername(username);user.setPassword(password);user.setName(name);user.setSex(sex);user.setEmail(email);user.setTel(tel);list.add(user);//System.out.println("userId="+userId);//return user;}} catch (SQLException e) {logger.info("Bad SQL Grammer", e);}return list;}/** *  * @description: 释放资源，注意：先开的后关，后开的先关 * @param rs * @param ps * @param conn */public void close(ResultSet rs, Statement st, Connection conn) {try {if (rs != null && rs.isClosed()) {rs.close();}} catch (SQLException e1) {e1.printStackTrace();}try {if (st != null && st.isClosed()) {st.close();}} catch (SQLException e) {e.printStackTrace();}try {if (conn != null && conn.isClosed()) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}public static void main(String[] args) {JdbcTest jt = new JdbcTest();User user = new User();user.setId((long) 1);// System.out.println(jt.PreparedStatementSave(user));// System.out.println(jt.update(user));//System.out.println(jt.delete((long) 1));System.out.println(jt.getAll(2l).get(0).toString());}}

package com.util.jdbc;public class User {private Long id;private String username;private String password;private String name;private String sex;private String email;private String tel;public User() {super();// TODO Auto-generated constructor stub}public User(Long id, String username, String password, String name,String sex, String email, String tel) {super();this.id = id;this.username = username;this.password = password;this.name = name;this.sex = sex;this.email = email;this.tel = tel;}public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getTel() {return tel;}public void setTel(String tel) {this.tel = tel;}@Overridepublic String toString() {return "User [id=" + id + ", username=" + username + ", password="+ password + ", name=" + name + ", sex=" + sex + ", email="+ email + ", tel=" + tel + "]";}//public String toString()}

看过的，觉得有用的请点个赞，小生这厢谢过啦！