1.需求说明
2.需求分析使用jdbc操作数据库
考察使用jdbc操作数据库的步骤,1)建立数据库连接的方法 2)执行SQL语句的对象3)获取查询结果集等
3.所需文件
无论使用什么方式操作数据库都需要相应数据库的驱动如:访问MySQL数据库需要MySQL对应的驱动、操作oracle数据库需要oracle的驱动;
本例使用MySQL数据库所以需要加入MySQL的驱动 mysql-connector-java-5.1.7-bin.jar
4.程序代码
4.1创建一个Java工程
4.2数据库表结构
数据库中有四张表此处我们查询user表如第二张图。
4.3在工程添加user表的实体类方便封装查询结果
对应代码:package com.bbk.jdbc.domain;
import java.util.Date;
public class User {
private int id;
private String username;
private Date birthday;
private Integer sex;
private String address;
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", birthday="
+ birthday + ", sex=" + sex + ", address=" + address + "]";
}
}4.4建立操作数据库的测试类junit
对应代码:4.5查询结果package com.bbk.jdbc.test;
import java.sql.Connection;//全部使用接口定义为了程序的通用性,MySQL、oracleimport java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;
import org.junit.Test;
import com.bbk.jdbc.domain.User;
public class JdbcTest {
@Testpublic void test() throws Exception {String url = "jdbc:mysql://localhost/mybatis?characterEncoding=utf-8";//数据库地址,及设置字符集String name = "root";//用户名String passWord = "123456";//密码
Connection connection = null;//Statement statement = null;//数据库操作集对象(注意参数要硬编写在SQL语句中)PreparedStatement preparedStatement = null;//预编译的statement优化数据库性能且可设置查询参数ResultSet resultSet = null;try {//1.加载数据库驱动Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接connection = DriverManager.getConnection(url, name, passWord);String sql = "SELECT * FROM USER WHERE username = ? OR username = ?";
//3.获取执行statement对象//statement = connection.createStatement();preparedStatement = connection.prepareStatement(sql);
//4.设置查询参数preparedStatement.setString(1, "王五");preparedStatement.setString(2, "张三");
//5.执行sql语句并获取结果集对象resultSet = preparedStatement.executeQuery();List<User> list = new ArrayList<User>();//遍历结果集,通过列名称获取对象并存入到对应的实体类中while(resultSet.next()){//list中user对象要每一次循环创建一个user对象,如果user对象在循环外层//因为list存取的是user的索引,第二次对user赋值会覆盖第一次存入list的user属性值User user = new User();user.setId(resultSet.getInt("id"));user.setUsername(resultSet.getString("username"));user.setBirthday(resultSet.getDate("birthday"));user.setSex(resultSet.getInt("sex"));user.setAddress(resultSet.getString("address"));list.add(user);}System.out.println(list);} catch (SQLException e) {e.printStackTrace();}finally{//关闭连接按照申请顺序关闭if (resultSet != null) {resultSet.close();}if (preparedStatement != null) {preparedStatement.close();}if (connection != null) {connection.close();}}}}
[User [id=1, username=王五, birthday=null, sex=2, address=null], User [id=10, username=张三, birthday=2014-07-10, sex=1, address=北京市], User [id=26, username=王五, birthday=null, sex=0, address=null]]