JDBC访问数据库
一、实验目的：
理解JDBC的工作原理，掌握JDBC访问数据库；
掌握常见数据库MYSQL ；
二、实验环境：
MyEclipse10+Tomcat 7.0+Java EE 6.0+MySQL+Navicat for MySQL
三、实验内容：
现在使用JDBC写一个简单的小程序，主要是使用JDBC连接MySQL数据库，然后对数据库进行一些基本的增删改查操作。
1、设计数据库表
首先新建一个数据库shiyan4，先设计一个数据库表，用于保存用户信息，建表语句如下：
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,
creat_dt DATE NOT NULL,
PRIMARY KEY(id)
)
ENGINE=INNODB DEFAULT CHARSET=utf8;在用户表中定义了几个字段，分别是id，user_name，age，sex，create_dt，其中id是主键，是自增长的，user_name表示用户名，age表示用户年龄，sex表示用户的性别，这里的性别用数字表示，0表示女性，1表示男性，create_dt表示创建的时间。预先在数据库中插入几条数据，数据如下。
2、定义实体类
首先新建一个Java Project。
定义一个Bean，与数据库表中的各个字段对应：新建一个class：UserVO.java，打包为com.imooc.jdbc;
UserVO.java的源代码如下：
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;
}
public String toString() {
return "UserVO[id="+id+",userName="+userName+",age="+age+
",sex="+sex+",createDt="+createDt+"]";
}
}
3、定义数据库连接类
定义一个数据库连接类，用于获取MySQL的连接。首先需要在当前的Java Project中导入MYSQL数据库的驱动程序文件：mysql-connector-java-5.1.47-bin.jar。然后在包com.imooc.jdbc下新建class：DBUtil.java
源代码如下：
package com.imooc.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtil {
private static final String URL="jdbc:mysql://localhost:3306/shiyan4?"+
"user=root&password=123456&useUnicode=true&characterEncoding=UTF-8";
private static final String DRIVER="com.mysql.jdbc.Driver";
public static Connection connectDB() throws Exception {
Class.forName(DRIVER);
Connection conn=DriverManager.getConnection(URL);
return conn;
}
}
MySQL的JDBC URL编写方式为：jdbc:mysql://主机名称：连接端口/数据库的名称?参数=值，在这个例子中我连接的数据库主机名称为localhost，端口为8080，在参数中指定用户名为root，密码也为123456，为了避免中文乱码要指定useUnicode和characterEncoding。因为连接的是MySQL数据库，所以程序一开始需要加载MySQL的数据库驱动，然后通过DriverManager.getConnection(String URL)方法获取数据库的连接。
4、实现数据库的增删改查
在获取了数据库的连接之后，就可以操作数据库了，下面分别实现数据库的增删改查操作，定义一个UserDao类用于操作数据库。
在包com.imooc.jdbc下新建class：UserDao.java。先导入所需要的包。
package com.imooc.jdbc;
import java.sql.*;
import java.sql.Date;
import java.util.*;
public class UserDao {
}
1）查询
先看查询操作，查询可以一次查询出所有的数据，也可以根据相应的条件查询。
查询所有的数据，在UserDao中定义一个queryAll()方法：在public class 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=(Statement) 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;
}
这里使用Connection.createStatement()方法获取一个Statement对象，这个对象里面有很多的方法可以操作数据库，使用excuteQuery(String sql)执行查询操作，查询结果为一个结果集ResultSet，可以通过这个结果集获取相关的信息。
测试：新建一个class：findAll.java，打包为com.zsgc。
findAll.java源代码如下：
package com.zsgc;
import java.util.List;
import com.imooc.jdbc.*;
public class findAll {
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();
}
}
}运行结果：
根据条件查询，在public class UserDao {}中定义一个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;
}这个方法可以自由选择查询的条件，只需要向方法中传入一个条件的List即可，这些条件都是由Map组成的，每一个Map包含三个元素，col表示查询条件对应哪一列，rel表示查询条件的关系是什么，value是指查询条件的值。这样写集成了多查询条件的方法，很多的业务下，查询的逻辑可能很多，这样写只用一个统一的方法就可以解决多种不同查询条件的业务逻辑。
测试：在包com.zsgc下新建findByParams.java，代码如下：
package com.zsgc;
import com.imooc.jdbc.*;
import java.util.*;
public class findByParams {
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();
}
}
}
在这个main方法中设定了两个查询条件，一是user_name like %John%，另一个是sex=1，当然条件也可以是其他的，执行程序运行结果为：
2）增加
在public class 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();
}这个方法使用Connection.prepareStatement(String sql)方法获取一个PreparedStatement对象，使用这个方法可以传入带参数的SQL语句，而参数的值可以通过PreparedStatement.setXXX(int index, XXX value)的方法指定，其中XXX为各种不同的类型，index指定第几个参数的下标。指定了参数的值之后，便可以执行excute()方法执行SQL语句了。
测试：在包com.zsgc下新建addUserInfo.java，代码如下：
package com.zsgc;
import com.imooc.jdbc.*;
public class addUserInfo {
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();
}
}
}运行结果：执行后再查看数据库，发现Tom这个用户已经插入成功了。
3）删除
在public class UserDao {}中添加deleteUser()方法，代码如下：
//删除
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();
}测试：在包com.zsgc下新建deleteUserInfo.java，代码如下：
package com.zsgc;
import com.imooc.jdbc.*;
public class deleteUserInfo {
public static void main(String[] args) {
UserDao dao=new UserDao();
try {
dao.deleteUser(7);
}catch(Exception e) {
e.printStackTrace();
}
}
}运行结果：删除id为7的用户，也就是刚刚用新增方法创建的用户名为Tom的这个用户，运行后查看数据库：
4）更新数据库
在public class UserDao {}中添加updateUser()方法，代码如下：
//更新
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();
}从SQL语句中可以看出更新也是根据用户的id进行选择性的更新的。
测试：在包com.zsgc下新建updateUserInfo.java，代码如下：
package com.zsgc;
import com.imooc.jdbc.*;
public class updateUserInfo {
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岁，执行程序，运行后查看数据库：
可以看到Mary的年龄确实变成了30，更新成功。
