JDBC的五个步骤及基本CRUD操作

使用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);
		}
	}
}