JDBC

JDBC

基本增删改：（SQL语句写死）

public static final String URL = "jdbc:mysql://localhost:3306/test";public static final String USERNAME = "root";public static final String PASSWORD = "root";/** * 插入 */public static void insert(){try {//1、加载数据库驱动Class.forName("com.mysql.jdbc.Driver");//2、获取数据库连接Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//3、构造SQL语句String sql = "insert into person(name,age,description)values('小白',18,'一个人')";//4、构造Statement实例 （用于发送sql语句载体）Statement state = conn.createStatement();//5、执行sql语句System.out.println(state.executeUpdate(sql)+"条语句被执行");//关闭连接   释放资源state.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}/** * 修改 */public static void update(){try {//1、加载数据库驱动Class.forName("com.mysql.jdbc.Driver");//2、获取数据库连接Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//3、构造SQL语句String sql = "update person set age =28 where id = 2";//4、构造Statement实例 （用于发送sql语句载体）Statement state = conn.createStatement();//5、执行sql语句System.out.println(state.executeUpdate(sql)+"条语句被执行");//关闭连接   释放资源state.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}/** * 删除 */public static void delete(){try {//1、加载数据库驱动Class.forName("com.mysql.jdbc.Driver");//2、获取数据库连接Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//3、构造SQL语句String sql = "delete from person where id = 3";//4、构造Statement实例 （用于发送sql语句载体）Statement state = conn.createStatement();//5、执行sql语句System.out.println(state.executeUpdate(sql)+"条语句被执行");//关闭连接   释放资源state.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}

SQL语句传值

/** * 增加 */public static void insert2(){String name = "小黑";String age = "35";String description = "两个人";try {//1、加载数据库驱动Class.forName("com.mysql.jdbc.Driver");//2、获取数据库连接Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//3、构造SQL语句String sql = "insert into person(name,age,description)values('"+name+"',"+age+",'"+description+"')";//4、构造Statement实例 （用于发送sql语句载体）Statement state = conn.createStatement();//5、执行sql语句System.out.println(state.executeUpdate(sql)+"条语句被执行");//关闭连接   释放资源state.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}

基本查询

public static final String URL = "jdbc:mysql://localhost:3306/test";public static final String USERNAME = "root";public static final String PASSWORD = "root";/** * 查询 */public static void query(){try {//1、加载数据库驱动Class.forName("com.mysql.jdbc.Driver");//2、获取数据库连接Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//3、构造SQL语句String sql = "select id,name,age,description from person";//4、构造Statement实例 （用于发送sql语句载体）Statement state = conn.createStatement();//5、执行sql语句ResultSet rs = state.executeQuery(sql);while(rs.next()){int id = rs.getInt("id");String name = rs.getString("name");int age = rs.getInt("age");String description = rs.getString("description");System.out.println("id="+id+",name="+name+"+age="+age+",description="+description);}//关闭连接   释放资源rs.close();state.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}

PreparedStatement

public static final String URL = "jdbc:mysql://localhost:3306/test";public static final String USERNAME = "root";public static final String PASSWORD = "root";/** * 插入 */public static void insert(){try {//1、加载数据库驱动Class.forName("com.mysql.jdbc.Driver");//2、获取数据库连接Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);//3、构造SQL语句String sql = "insert into person(name,age,description)values(?,?,?)";//4、构造Statement实例 （用于发送sql语句载体）PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1, "马汉三");ps.setInt(2, 53);ps.setString(3, "一个人");//5、执行sql语句System.out.println(ps.executeUpdate()+"条语句被执行");//关闭连接   释放资源ps.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}

事务

public static final String URL = "jdbc:mysql://localhost:3306/test";public static final String USERNAME = "root";public static final String PASSWORD = "root";/** * 事务 */public static void insert(){Connection conn = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);conn.setAutoCommit(false);//设置手动提交String sql1 = "insert into person(name,age,description)values(?,?,?)";String sql2 = "update person set name=? where id=?";PreparedStatement ps = conn.prepareStatement(sql1);ps.setString(1, "方孟敖");ps.setInt(2, 53);ps.setString(3, "一个人");ps.executeUpdate();ps = conn.prepareStatement(sql2);ps.setString(1, "方就停");ps.setInt(2, 6);ps.executeUpdate();conn.commit();//提交//关闭连接   释放资源ps.close();conn.close();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();try {conn.rollback();//回滚} catch (SQLException e1) {e1.printStackTrace();}}}

封装JDBC工具类

DBUtils.java

package com.java;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtils {//数据库链接地址public static final String URL = "jdbc:mysql://localhost:3306/test";//用户名public static final String USERNAME = "root";//密码public static final String PASSWORD = "root";//驱动信息public static final String DRIVER = "com.mysql.jdbc.Driver";//私有构造方法private DBUtils() {}//使用静态代码块加载驱动static{try {Class.forName(DRIVER);} catch (ClassNotFoundException e) {e.printStackTrace();}}//定义一个获取数据库连接的方法public static Connection getConnection(){Connection conn = null;try {conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);} catch (SQLException e) {e.printStackTrace();System.out.println("获取数据库连接失败");}return conn;}//关闭数据库连接资源public static void close(ResultSet rs,Statement stat,Connection conn){try {if(rs!=null)rs.close();if(stat!=null)stat.close();if(conn!=null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}

Test.java

package com.java;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class Test {/** * 查询方法 */public static void findAll(){//通过工具类获取数据库连接Connection conn = DBUtils.getConnection();PreparedStatement ps = null;ResultSet rs = null;String sql = "select name,age,description from person";try {ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()){String name = rs.getString("name");int age = rs.getInt("age");String description = rs.getString("description");System.out.println(name+age+description);}} catch (SQLException e) {e.printStackTrace();}finally{DBUtils.close(rs, ps, conn);}}public static void main(String[] args) {findAll();}}

DAO模式

典型到实现由以下几个组件：

• 一个DAO接口
• 一个DAO实现类
• 数据传递对象（DTO）、或也叫值对象（VO）、或领域模型（domain）

PersonDao.java

package com.java.dao;import java.sql.SQLException;import java.util.List;import com.java.db.Person;public interface PersonDao {//添加方法public void add(Person p)throws SQLException;//更新方法public void update(Person p)throws SQLException;//删除方法public void delete(int id)throws SQLException;//查找方法public Person findById(int id)throws SQLException;//查找所有public List<Person> findAll()throws SQLException;}

PersonDaoImpl.java

package com.java.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.java.dao.PersonDao;import com.java.db.DBUtils;import com.java.db.Person;/** * 实现类 */public class PersonDaoImpl implements PersonDao{//实现添加方法@Overridepublic void add(Person p) throws SQLException {Connection conn = null;PreparedStatement ps = null;String sql = "insert into person(name,age,description)values(?,?,?)";try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);ps.setString(1, "王小五");ps.setInt(2, 50);ps.setString(3, "小流氓");ps.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally{DBUtils.close(null, ps, conn);}}//实现删除方法@Overridepublic void delete(int id) throws SQLException {Connection conn = null;PreparedStatement ps = null;String sql = "delete from person where id=?";try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);ps.setInt(1, id);ps.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally{DBUtils.close(null, ps, conn);}}//查询所有数据@Overridepublic List<Person> findAll() throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;Person p = null;List<Person> personList = new ArrayList<Person>();String sql = "select id,name,age,description from person";try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()){p = new Person();p.setId(rs.getInt("id"));p.setName(rs.getString("name"));p.setAge(rs.getInt("age"));p.setDescription(rs.getString("description"));personList.add(p);}} catch (Exception e) {e.printStackTrace();}finally{DBUtils.close(rs, ps, conn);}return personList;}//根据id查询一个对象@Overridepublic Person findById(int id) throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;Person p = null;String sql = "select name,age,description from person where id=?";try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);ps.setInt(1, id);rs = ps.executeQuery();if(rs.next()){p = new Person();p.setName(rs.getString("name"));p.setAge(rs.getInt("age"));p.setDescription(rs.getString("description"));}} catch (Exception e) {e.printStackTrace();}finally{DBUtils.close(rs, ps, conn);}return p;}//实现修改方法@Overridepublic void update(Person p) throws SQLException {Connection conn = null;PreparedStatement ps = null;String sql = "update person set name=?,age=?,description=? where id=?";try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);ps.setString(1, "王大五");ps.setInt(2, 50);ps.setString(3, "大流氓");ps.setInt(4, p.getId());ps.executeUpdate();} catch (Exception e) {e.printStackTrace();}finally{DBUtils.close(null, ps, conn);}}}

DBUtils.java

package com.java.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtils {//数据库链接地址public static final String URL = "jdbc:mysql://localhost:3306/test";//用户名public static final String USERNAME = "root";//密码public static final String PASSWORD = "root";//驱动信息public static final String DRIVER = "com.mysql.jdbc.Driver";//私有构造方法private DBUtils() {}//使用静态代码块加载驱动static{try {Class.forName(DRIVER);} catch (ClassNotFoundException e) {e.printStackTrace();}}//定义一个获取数据库连接的方法public static Connection getConnection(){Connection conn = null;try {conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);} catch (SQLException e) {e.printStackTrace();System.out.println("获取数据库连接失败");}return conn;}//关闭数据库连接资源public static void close(ResultSet rs,Statement stat,Connection conn){try {if(rs!=null)rs.close();if(stat!=null)stat.close();if(conn!=null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}

Person.java

package com.java.db;public class Person {private int id;private String name;private int age;private String description;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}}

DAO模式(增删改优化)

JdbcTemplete.java

package com.java.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import com.java.db.DBUtils;public class JdbcTemplete {/** * 实现增删改的抽象 * @param sql * @param args */public int update(String sql,Object...args)throws SQLException{Connection conn = null;PreparedStatement ps = null;try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);if(args != null){for(int i=0;i<args.length;i++){ps.setObject(i+1, args[i]);}}return ps.executeUpdate();} catch (Exception e) {e.printStackTrace();return -1;}finally{DBUtils.close(null, ps, conn);}}}

PersonDaoImpl.java

package com.java.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.java.dao.PersonDao;import com.java.db.DBUtils;import com.java.db.Person;/** * 实现类 */public class PersonDaoImpl implements PersonDao{private JdbcTemplete jdbcTemplete;public PersonDaoImpl() {jdbcTemplete = new JdbcTemplete();}//实现添加方法@Overridepublic void add(Person p) throws SQLException {String sql = "insert into person(name,age,description)values(?,?,?)";jdbcTemplete.update(sql, p.getName(),p.getAge(),p.getDescription());}//实现修改方法@Overridepublic void update(Person p) throws SQLException {String sql = "update person set name=?,age=?,description=? where id=?";jdbcTemplete.update(sql, p.getName(),p.getAge(),p.getDescription(),p.getId());}//实现删除方法@Overridepublic void delete(int id) throws SQLException {String sql = "delete from person where id=?";jdbcTemplete.update(sql,id);}//查询所有数据@Overridepublic List<Person> findAll() throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;Person p = null;List<Person> personList = new ArrayList<Person>();String sql = "select id,name,age,description from person";try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()){p = new Person();p.setId(rs.getInt("id"));p.setName(rs.getString("name"));p.setAge(rs.getInt("age"));p.setDescription(rs.getString("description"));personList.add(p);}} catch (Exception e) {e.printStackTrace();}finally{DBUtils.close(rs, ps, conn);}return null;}//根据id查询一个对象@Overridepublic Person findById(int id) throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;Person p = null;String sql = "select name,age,description from person where id=?";try {conn = DBUtils.getConnection();ps = conn.prepareStatement(sql);ps.setInt(1, id);rs = ps.executeQuery();if(rs.next()){p = new Person();p.setName(rs.getString("name"));p.setAge(rs.getInt("age"));p.setDescription(rs.getString("description"));}} catch (Exception e) {e.printStackTrace();}finally{DBUtils.close(rs, ps, conn);}return null;}}

开源工具DbUtils的使用  (最终的)

PersonDao.java

package com.java.dao;import java.sql.SQLException;import java.util.List;import com.java.domain.Person;public interface PersonDao {//添加方法public void add(Person p)throws SQLException;//更新方法public void update(Person p)throws SQLException;//删除方法public void delete(int id)throws SQLException;//查找方法public Person findById(int id)throws SQLException;//查找所有public List<Person> findAll()throws SQLException;//查询所有记录数public Long personCount() throws SQLException;}

PersonDaoImpl.java

package com.java.dao.impl;import java.sql.SQLException;import java.util.List;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import com.java.dao.PersonDao;import com.java.domain.DBUtils;import com.java.domain.Person;/** * 实现类 */public class PersonDaoImpl implements PersonDao{private QueryRunner queryRunner = null;  //查询运行器public PersonDaoImpl() {queryRunner = new QueryRunner();}//增加方法@Overridepublic void add(Person p) throws SQLException {String sql = "insert into person(name,age,description)values(?,?,?)";queryRunner.update(DBUtils.getConnection(), sql, p.getName(),p.getAge(),p.getDescription());}// 修改方法@Overridepublic void update(Person p) throws SQLException {String sql = "update person set name=?,age=?,description=? where id=?";queryRunner.update(DBUtils.getConnection(), sql, p.getName(),p.getAge(),p.getDescription(),p.getId());}//删除方法@Overridepublic void delete(int id) throws SQLException {String sql = "delete from person where id=?";queryRunner.update(DBUtils.getConnection(), sql, id);}//使用BeanHandler查询一个对象@Overridepublic Person findById(int id) throws SQLException {String sql = "select name,age,description from person where id=?";Person p = queryRunner.query(DBUtils.getConnection(), sql, new BeanHandler<Person>(Person.class),id);return p;}//查询对象列表@Overridepublic List<Person> findAll() throws SQLException {String sql = "select name,age,description from person";List<Person> ps= queryRunner.query(DBUtils.getConnection(), sql, new BeanListHandler<Person>(Person.class));return ps;}//返回总数public Long personCount() throws SQLException {String sql = "select count(id) from person";return queryRunner.query(DBUtils.getConnection(),sql, new ScalarHandler<Long>());}}

DBUtils.java

package com.java.domain;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBUtils {//数据库链接地址public static final String URL = "jdbc:mysql://localhost:3306/test";//用户名public static final String USERNAME = "root";//密码public static final String PASSWORD = "root";//驱动信息public static final String DRIVER = "com.mysql.jdbc.Driver";//私有构造方法private DBUtils() {}//使用静态代码块加载驱动static{try {Class.forName(DRIVER);} catch (ClassNotFoundException e) {e.printStackTrace();}}//定义一个获取数据库连接的方法public static Connection getConnection(){Connection conn = null;try {conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);} catch (SQLException e) {e.printStackTrace();System.out.println("获取数据库连接失败");}return conn;}//关闭数据库连接资源public static void close(ResultSet rs,Statement stat,Connection conn){try {if(rs!=null)rs.close();if(stat!=null)stat.close();if(conn!=null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}

Person.java

package com.java.domain;public class Person {private int id;private String name;private int age;private String description;public Person() {}public Person(String name, int age, String description) {this.name = name;this.age = age;this.description = description;}public Person(int id, String name, int age, String description) {this.id = id;this.name = name;this.age = age;this.description = description;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}}

Test.java

package com.java.domain;import java.sql.SQLException;import java.util.List;import com.java.dao.PersonDao;import com.java.dao.impl.PersonDaoImpl;public class Test {public static void main(String[] args) throws SQLException {PersonDao personDao = new PersonDaoImpl();//添加一条信息personDao.add(new Person("李四", 15, "小朋友"));//修改一条数据personDao.update(new Person(10,"老大", 15, "小朋友"));//删除一条数据personDao.delete(5);//查询id为5的数据Person p = personDao.findById(4);System.out.println(p);//查询所有数据List<Person> personList= personDao.findAll();System.out.println(personList);//查询数据总数Long i = personDao.personCount();System.out.println(i);}}