一、什么是JDBC
Java 数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。(摘自维基百科)
二、简单的JDBC实例
现在使用JDBC写一个简单的小程序,主要是使用JDBC连接MySQL数据库,然后对数据库进行一些基本的增删改查操作。
1、设计数据库表
先设计一个数据库表,用于保存用户信息,建表语句如下:
- CREATE TABLE
- tbl_user_info
- (
- id INT NOT NULL AUTO_INCREMENT,
- user_name VARCHAR(20) NOT NULL,
- age INT NOT NULL,
- sex INT(1) NOT NULL,
- create_dt DATE NOT NULL,
- PRIMARY KEY (id)
- )
- ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、定义实体类
定义一个Bean,与数据库表中的各个字段对应:
- package com.imooc.jdbc;
- import java.util.Date;
- public class UserVO {
- private int id;
- private String userName;
- private int age;
- private int sex;
- private Date createDt;
- public int getId() {
- return id;
- }
- public void setId(int id) {
- this.id = id;
- }
- public String getUserName() {
- return userName;
- }
- public void setUserName(String userName) {
- this.userName = userName;
- }
- public int getAge() {
- return age;
- }
- public void setAge(int age) {
- this.age = age;
- }
- public int getSex() {
- return sex;
- }
- public void setSex(int sex) {
- this.sex = sex;
- }
- public Date getCreateDt() {
- return createDt;
- }
- public void setCreateDt(Date createDt) {
- this.createDt = createDt;
- }
- @Override
- public String toString() {
- return "UserVO [id=" + id + ", userName=" + userName + ", age=" + age
- + ", sex=" + sex + ", createDt=" + createDt + "]";
- }
- }
3、定义数据库连接类
定义一个数据库连接类,用于获取MySQL的连接- package com.imooc.jdbc;
- import java.sql.Connection;
- import java.sql.DriverManager;
- public class DBUtil {
- private static final String URI = "jdbc:mysql://192.168.2.207:3306/jdbc_demo?"
- + "user=root&password=root&useUnicode=true&characterEncoding=UTF-8";
- private static final String DRIVER = "com.mysql.jdbc.Driver";
- public static Connection connectDB() throws Exception {
- //1、加载数据库驱动
- Class.forName(DRIVER);
- //2、获取数据库连接
- Connection conn = DriverManager.getConnection(URI);
- return conn;
- }
- }
1)查询
先看查询操作,查询可以一次查询出所有的数据,也可以根据相应的条件查询。
查询所有的数据,在UserDao中定义一个queryAll()方法:
- public List<UserVO> queryAll() throws Exception {
- Connection conn = DBUtil.connectDB();
- String sql = "SELECT * FROM tbl_user_info";
- List<UserVO> userList = new ArrayList<UserVO>();
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(sql);
- while(rs.next()) {
- UserVO user = new UserVO();
- user.setId(rs.getInt("id"));
- user.setUserName(rs.getString("user_name"));
- user.setAge(rs.getInt("age"));
- user.setSex(rs.getInt("sex"));
- user.setCreateDt(rs.getDate("create_dt"));
- userList.add(user);
- }
- return userList;
- }
定义main函数:
- public static void main(String[] args) {
- UserDao dao = new UserDao();
- try {
- List<UserVO> userList = dao.queryAll();
- for(UserVO user : userList) {
- System.out.println(user);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
UserVO [id=4, userName=Mary, age=25, sex=0, createDt=2016-06-24]
UserVO [id=5, userName=Jack, age=22, sex=1, createDt=2016-06-24]
UserVO [id=6, userName=John, age=19, sex=1, createDt=2016-06-24]
根据条件查询,定义一个queryByParams方法:
UserVO [id=5, userName=Jack, age=22, sex=1, createDt=2016-06-24]
UserVO [id=6, userName=John, age=19, sex=1, createDt=2016-06-24]
根据条件查询,定义一个queryByParams方法:
- public List<UserVO> queryByParams(List<Map<String, Object>> params) throws Exception {
- Connection conn = DBUtil.connectDB();
- StringBuilder sql = new StringBuilder("SELECT * FROM tbl_user_info WHERE 1=1 ");
- for(Map<String, Object> param : params) {
- sql.append(" and ");
- sql.append(" " + param.get("col") + " ");
- sql.append(" " + param.get("rel") + " ");
- sql.append(" " + param.get("value") + " ");
- }
- System.out.println(sql.toString());
- List<UserVO> userList = new ArrayList<UserVO>();
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(sql.toString());
- while(rs.next()) {
- UserVO user = new UserVO();
- user.setId(rs.getInt("id"));
- user.setUserName(rs.getString("user_name"));
- user.setAge(rs.getInt("age"));
- user.setSex(rs.getInt("sex"));
- user.setCreateDt(rs.getDate("create_dt"));
- userList.add(user);
- }
- return userList;
- }
再写一个简单的main函数测试一下:
- public static void main(String[] args) {
- UserDao dao = new UserDao();
- List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();
- Map<String, Object> param1 = new HashMap<String, Object>();
- param1.put("col", "user_name");
- param1.put("rel", "like");
- param1.put("value", "'%John%'");
- params.add(param1);
- Map<String, Object> param2 = new HashMap<String, Object>();
- param2.put("col", "sex");
- param2.put("rel", "=");
- param2.put("value", 1);
- params.add(param2);
- try {
- List<UserVO> userList = dao.queryByParams(params);
- for(UserVO user : userList) {
- System.out.println(user);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
SELECT * FROM tbl_user_info WHERE 1=1 and user_name like '%John%' and sex = 1
UserVO [id=6, userName=John, age=19, sex=1, createDt=2016-06-24]
UserVO [id=6, userName=John, age=19, sex=1, createDt=2016-06-24]
2)增加
现在在UserDao中写一个addUser方法用于新增一条信息:- public void addUser(UserVO user) throws Exception {
- Connection conn = DBUtil.connectDB();
- String sql = "INSERT INTO tbl_user_info(user_name, age, sex, create_dt) "
- + " VALUES(?, ?, ?, ?)";
- PreparedStatement pstmt = conn.prepareStatement(sql);
- pstmt.setString(1, user.getUserName());
- pstmt.setInt(2, user.getAge());
- pstmt.setInt(3, user.getSex());
- pstmt.setDate(4, new Date(new java.util.Date().getTime()));
- pstmt.execute();
- }
接下来写一个main方法来验证这个增加的方法:
- public static void main(String[] args) {
- UserDao dao = new UserDao();
- UserVO user = new UserVO();
- user.setUserName("Tom");
- user.setAge(20);
- user.setSex(1);
- try {
- dao.addUser(user);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
3)删除
接下来再写一个删除的方法,根据用户的id来删除数据:
- public void deleteUser(int id) throws Exception {
- Connection conn = DBUtil.connectDB();
- String sql = "DELETE FROM tbl_user_info WHERE id = ?";
- PreparedStatement pstmt = conn.prepareStatement(sql);
- pstmt.setInt(1, id);
- pstmt.execute();
- }
- public static void main(String[] args) {
- UserDao dao = new UserDao();
- try {
- dao.deleteUser(7);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
4)更新数据库
最后来看一下更新数据库:
- public void updateUser(UserVO user) throws Exception {
- Connection conn = DBUtil.connectDB();
- String sql = "UPDATE tbl_user_info SET user_name=?, age=?, sex=?"
- + " WHERE id=?";
- PreparedStatement pstmt = conn.prepareStatement(sql);
- pstmt.setString(1, user.getUserName());
- pstmt.setInt(2, user.getAge());
- pstmt.setInt(3, user.getSex());
- pstmt.setInt(4, user.getId());
- pstmt.executeUpdate();
- }
写一个main方法来验证:
- public static void main(String[] args) {
- UserDao dao = new UserDao();
- UserVO user = new UserVO();
- user.setUserName("Mary");
- user.setAge(30);
- user.setSex(0);
- user.setId(4);
- try {
- dao.updateUser(user);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
可以看到Mary的年龄确实变成了30,,删除成功。
三、结语
由此使用JDBC连接MySQL数据库并进行基本的增删改查操作就已经完成了,这些只是最简单的数据库操作,实际开发过程中操作数据库比这些要复杂得多,包括事务的处理、存储过程等等,那就需要使用JDBC更高级的功能了,这些下次再写。