使用jdbc对数据库的crud操作的五个基本步骤:
1、加载驱动
2、创建连接
3、编写sql
4、预编译sql
5、返回结果集
具体代码示例如下,我使用的是oracle数据库:
1、创建一个DBUtils工具类
package com.zzy.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static{
try {
//加载配置文件
InputStream in = DBUtils.class.getClassLoader().getResourceAsStream("DB.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//加载驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return Connection
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url,username,password);
}
/**
* 关闭资源
* @param resultSet
* @param statement
* @param connection
*/
public static void releaseResources(ResultSet resultSet,Statement statement,Connection connection){
try {
if(resultSet != null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
resultSet = null;
try {
if(statement != null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
statement = null;
try {
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
connection = null;
}
}
}
}
}
2、DB.properties配置文件内容
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
username=orcl
password=orcl
3、创建News类
package com.zzy.bean;
public class News {
private int newsId;
private String title;
private String content;
private String author;
private String createTime;
private int themeId;
public int getNewsId() {
return newsId;
}
public void setNewsId(int newsId) {
this.newsId = newsId;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public int getThemeId() {
return themeId;
}
public void setThemeId(int themeId) {
this.themeId = themeId;
}
@Override
public String toString() {
return "News [newsId=" + newsId + ", title=" + title + ", content="
+ content + ", author=" + author + ", createTime=" + createTime
+ ", themeId=" + themeId + "]";
}
}
4、使用PreparedStatement对象完成对数据库的CRUD操作
package com.zzy.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.zzy.bean.News;
import com.zzy.util.DBUtils;
public class JDBCTest {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
/**
* 从数据库中查询一条语句
*/
@Test
public void select(){
try {
//获取数据库连接
conn = DBUtils.getConnection();
//编写sql语句
String sql = "select * from news where newsid = ?";
//预编译语句
pst = conn.prepareStatement(sql);
//为sql中的参数赋值,索引从1开始
pst.setInt(1, 21);
//执行查询语句
rs = pst.executeQuery();
News news = null;
if(rs.next()){
news = new News();
news.setNewsId(rs.getInt(1));
news.setTitle(rs.getString(2));
news.setContent(rs.getString(3));
news.setAuthor(rs.getString(4));
news.setCreateTime(rs.getString(5));
news.setThemeId(rs.getInt(6));
System.out.println(news);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.releaseResources(rs, pst, conn);
}
}
/**
* 往数据库中插入一条数据
*/
@Test
public void insert(){
try {
//获取数据库连接
conn = DBUtils.getConnection();
//编写sql语句
String sql = "insert into news (newsid,title,content,author,createtime,themeid) values(seq_news.nextval,?,?,?,?,?)";
//预编译语句
pst = conn.prepareStatement(sql);
//为sql中的参数赋值,索引从1开始
pst.setString(1, "新闻标题");
pst.setString(2, "新闻内容");
pst.setString(3, "新闻作者");
pst.setString(4, "创建时间");//pst.setDate(4, new java.sql.Date(new Date().getTime()));//如果createtime是date类型
pst.setInt(5, 1);
//执行插入操作,executeUpdate方法返回成功的条数
int num = pst.executeUpdate();
if(num>0){
System.out.println("成功插入"+num+"条数据");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.releaseResources(rs, pst, conn);
}
}
/**
* 修改表中的一条数据
*/
@Test
public void update(){
try {
conn = DBUtils.getConnection();
String sql = "update news set title=?,content=? where newsid=?";
pst = conn.prepareStatement(sql);
pst.setString(1, "新闻标题");
pst.setString(2, "新闻内容");
pst.setInt(3, 21);
int num = pst.executeUpdate();
if(num>0){
System.out.println("成功修改"+num+"条数据");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.releaseResources(rs, pst, conn);
}
}
/**
* 删除表中的一条数据
*/
@Test
public void delete(){
try {
conn = DBUtils.getConnection();
String sql = "delete from news where newsid=?";
pst = conn.prepareStatement(sql);
pst.setInt(1, 1);
int num = pst.executeUpdate();
if(num > 0){
System.out.println("成功删除"+num+"条数据");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.releaseResources(rs, pst, conn);
}
}
}