JDBC访问数据库的步骤和增删改查

（1）加载JDBC驱动程序。

（2）建立数据库连接（即Connection对象）。

（3）创建语句（即Statement/PreparedStatement/CallableStatement对象）。

（4）执行语句（对应于SQL语句命令）。

（5）如果为查询SQL语句命令，则循环处理其结果集。

（6）关闭数据库连接。

例如：

 

		//1.注册数据库驱动
		DriverManager.registerDriver(new Driver());
		//2.获取数据库连接
		Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/day10", "root", "root");
		//3.获取传输器对象
		Statement stat=conn.createStatement();
		//4.利用传输器传输sql语句到数据库中执行，获取结果集对象
		ResultSet rs=stat.executeQuery("select * from user");
		//5.遍历结果集获取查询结果 
		while(rs.next()){
			String name = rs.getString("name");
			System.out.println(name);
		}
		//6.关闭资源
		rs.close();
		stat.close();
		conn.close();

一般不用上面的写法：

 

		//1.注册数据库驱动
		//DriverManager.registerDriver(new Driver());
		Class.forName("com.mysql.jdbc.Driver");
		//2.获取数据库连接
		Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/day10", "root", "root");
		//3.获取传输器对象
		Statement stat=conn.createStatement();
		//4.利用传输器传输sql语句到数据库中执行，获取结果集对象
		ResultSet rs=stat.executeQuery("select * from user");
		//5.遍历结果集获取查询结果 
		while(rs.next()){
			String name = rs.getString("name");
			System.out.println(name);
		}
		//6.关闭资源
		rs.close();
		stat.close();
		conn.close();

JDBC查询代码：

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.PreparedStatement;

public class JDBCDemo1{
	public static void main(String[] args) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");//1.注册数据库驱动			
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day10", "root", "root"); //2.获取数据库连接
			stat = conn.createStatement();//3.获取传输器对象
			rs = stat.executeQuery("select * from user");//4.获取结果集对象
			while(rs.next()){//5.遍历结果集获取查询结果
				String name = rs.getString("name");
				System.out.println(name);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			//6.关闭资源
			if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null;
			}
			}
			if(stat!=null){
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				stat = null;
			}
			}
			if(conn!= null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				conn = null;
			}
			}
		}
	}
}

JDBC JUnit测试增删改查：

 

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

import com.itheima.util.JDBCUtils;

public class JDBCDemo2{
	@Test
	public void delete(){
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try{
			conn = JDBCUtils.getConn();
			stat =  conn.createStatement();
			stat.executeUpdate("delete from user where name='zhaoliu'");
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.close(rs, stat, conn);
		}	
	}
	@Test
	public void find(){
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		try{
			conn = JDBCUtils.getConn();
			stat = conn.createStatement();
			rs = stat.executeQuery("select * from user where name='zhaoliu'");
			while(rs.next()){
				String name = rs.getString("name");
				String password = rs.getString("password");
				System.out.println(name+":"+password);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.close(rs, stat, conn);
		}
		
	}
	@Test
	public void update(){
		Connection conn = null;
		Statement stat = null;
		try{
			conn = JDBCUtils.getConn();
			stat = conn.createStatement();
			stat.executeUpdate("update user set password=666 where name='zhaoliu'");
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.close(null, stat, conn);
		}
	} 
	@Test
	public void add(){
		Connection conn = null;
		Statement stat = null;
		try{
			//1.注册数据库驱动2.获取连接
			conn = JDBCUtils.getConn();
			//3.获取传输器对象
			stat = conn.createStatement();
			//4.执行SQL语句
			int count = stat.executeUpdate("insert into user values (null,'zhaoliu','987654','huangqi@qq.com','1887-09-12')");
			//5.处理结果
			if(count>0){
				System.out.println("执行成功！影响的行数为"+ count );
			}else{
				System.out.println("执行失败！");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtils.close(null, stat, conn);
		}
	}
}

JDBCUtils工具类：

 

 

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.management.RuntimeErrorException;

public class JDBCUtils{
	private static Properties prop = null;
	private JDBCUtils(){
		}
	static{
		try{
			prop = new Properties();
			prop.load(new FileReader(JDBCUtils.class.getClassLoader().getResource("config.properties").getPath()));
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	/**
	 * 获取连接
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConn() throws ClassNotFoundException, SQLException{
		Class.forName(prop.getProperty("driver"));
		return DriverManager.getConnection(prop.getProperty("url"),prop.getProperty("user"),prop.getProperty("password"));
		
	}
	/**
	 * 关闭连接
	 */
	public static void close(ResultSet rs, Statement stat,Connection conn){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null;
			}
			}
			if(stat!=null){
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				stat = null;
			}
			}
			if(conn!= null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				conn = null;
			}
		}
	}
}

src目录下，写配置文件，方便日后更改数据库。

 

起名：config.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/day10
user=root
password=root