JDBC的使用主要就是掌握四个对象:
1.DriverManager:用于注册驱动
2.Connection:与数据库建立连接
3.Statement:操作数据库执行语句的对象
4.ResultSet:返回的结果集
先创建数据库,有了数据库才能操作,本章中所有操作都围绕这个表做案例
package cn.lenovo.jdbcdemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) throws Exception {
// 注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 获取连 接
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/JDBCDemo", "root", "221210");
// 得到执行sql语句对象的Statment
Statement createStatement = connection.createStatement();
// 执行sql语句,并返回结果
ResultSet result = createStatement
.executeQuery("select * from student");
// 处理结果
while (result.next()) {
System.out.print(result.getObject(1));
System.out.print(result.getObject(2));
System.out.print(result.getObject(3));
System.out.print(result.getObject(4) + "\r\n");
}
// 关闭资源
result.close();
createStatement.close();
connection.close();
}
}
在注册驱动的时候,用DriverManager.registerDriver(new com.mysql.jdbc.Driver());
有一个弊端,就是导致注册两遍!为了解决这个问题我们替换为Class.forName("com.mysql.jdbc.Driver");
例如:
package cn.lenovo.jdbcdemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class JdbcDemo02 {
public static void main(String[] args) throws Exception {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获得Connection连接对象
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "221210");
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/JDBCDemo", info);
// 获得Statment处理sql语句
Statement createStatement = connection.createStatement();
// 执行SQL语句
ResultSet executeQuery = createStatement
.executeQuery("select * from student");
// 处理返回数据
while (executeQuery.next()) {
System.out.print(executeQuery.getObject("id"));
System.out.print(executeQuery.getObject("name"));
System.out.print(executeQuery.getObject("age"));
System.out.println(executeQuery.getObject("sex"));
}
// 关闭数据连接
executeQuery.close();
createStatement.close();
connection.close();
}
}
JDBC在用的时候,我们都会建立一个与数据库表相对应的Bean,
比如我已经有了一个Student的数据库表,同时我就要有一个Student的类,里面的属性与数据库相对应
(字段名字最好与数据库保持一致)
相对应的Student类
package cn.lenovo.jdbcdemo;
import java.sql.Date;
public class Student {
private int id;
private String name;
private int age;
private String sex;
private Date date;
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Student() {
super();
}
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age
+ ", sex=" + sex + ", data=" + date + "]";
}
}
JDBC测试类
package cn.lenovo.jdbcdemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
public class StudentSelect {
@Test
public void Test() throws Exception {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获得Connection连接对象
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/JDBCDemo", "root", "221210");
//获得Statment对象
Statement createStatement = connection.createStatement();
//用获得的Statment对象执行SQL语句后返回ResultSet结果集对象
ResultSet executeQuery = createStatement
.executeQuery("select * from student");
executeQuery.last();//游标移到末尾
//创建list装Student对象
List<Student> list = new ArrayList<Student>();
//循环将值设置到对象中,并添加到list中
while (executeQuery.previous()) {
Student student = new Student();
student.setId(executeQuery.getInt("id"));
student.setName(executeQuery.getString("name"));
student.setAge(executeQuery.getInt("age"));
student.setSex(executeQuery.getString("sex"));
student.setDate(executeQuery.getDate("date"));
list.add(student);
}
//关闭资源
executeQuery.close();
createStatement.close();
connection.close();
//迭代出List中的元素
for (Student student : list) {
System.out.println(student);
}
}
}
综合案例:对数据库实现CRUD的操作
配合使用properties将扩展性进一步提高
做此案例之时,我已经创建好了一个数据库表,本文开头那个
1.新建一个dbinfo.properties文件并配置好
driverClass=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/JDBCDemo
username=root
password=221210
2.新建Student类
package cn.lenovo.cruddbutils;
import java.sql.Date;
public class Student {
private int id;
private String name;
private int age;
private String sex;
private Date date;
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Student() {
super();
}
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", age=" + age
+ ", sex=" + sex + ", data=" + date + "]";
}
}
3.新建DBUtils工具类
package cn.lenovo.cruddbutils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class DBUtils {
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
// 加载配置文件数据的
ResourceBundle bundle = ResourceBundle.getBundle("dbinfo");
driverClass = bundle.getString("driverClass");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 获得connection连接
public static Connection getConnection() throws Exception {
Connection connection = DriverManager.getConnection(url, username,
password);
return connection;
}
// 关闭资源
public static void colseAll(ResultSet s, Statement t, Connection c) {
if (s != null) {
try {
s.close();
} catch (SQLException e) {
e.printStackTrace();
}
s = null;
}
if (t != null) {
try {
t.close();
} catch (SQLException e) {
e.printStackTrace();
}
t = null;
}
if (c != null) {
try {
c.close();
} catch (SQLException e3) {
e3.printStackTrace();
}
c = null;
}
}
}
4.创建测试类
package cn.lenovo.cruddbutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
public class CRUD {
ResultSet resultSet = null;
Statement statement = null;
Connection connection = null;
/**
* 查询
*/
@Test
public void testSelect() {
try {
connection = DBUtils.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from student");
List<Student> list = new ArrayList<Student>();
while (resultSet.next()) {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setAge(resultSet.getInt("age"));
student.setSex(resultSet.getString("sex"));
student.setDate(resultSet.getDate("date"));
list.add(student);
}
for (Student student : list) {
System.out.println(student);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.colseAll(resultSet, statement, connection);
}
}
/**
* 删除
*/
@Test
public void testDelete() {
try {
connection = DBUtils.getConnection();
statement = connection.createStatement();
statement.executeUpdate("delete from student where name='伊利丹,怒风'");
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.colseAll(null, statement, connection);
}
}
/**
* 更该数据
*/
@Test
public void testupDate() {
try {
connection = DBUtils.getConnection();
statement = connection.createStatement();
statement
.executeUpdate("update student set name='杜隆坦' where id='12'");
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.colseAll(null, statement, connection);
}
}
/**
* 添加数据
*/
@Test
public void testAdd() {
try {
connection = DBUtils.getConnection();
statement = connection.createStatement();
statement
.executeUpdate("insert into student (name,age,sex,date) values ('伊利丹·怒风',1400,'男','1000-00-02')");
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.colseAll(null, statement, connection);
}
}
}