《JDBC_CURD》
一、使用Statement
-
向数据库中增加记录
public void demo1(){ Connection conn = null; Statement stmt = null; try{ // 注册驱动: Class.forName("com.mysql.jdbc.Driver"); // 获得连接: conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc"); // 获得执行SQL语句的对象: stmt = conn.createStatement(); // 编写SQL: String sql = "insert into user values (null,'eee','123','张三')"; // 执行SQL: int i = stmt.executeUpdate(sql); if(i > 0){ System.out.println("保存成功!"); } }catch(Exception e){ e.printStackTrace(); }finally{ // 释放资源: if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } }
-
向数据库中删除记录
public void demo3(){ Connection conn = null; Statement stmt = null; try{ // 注册驱动 Class.forName("com.mysql.jdbc.Driver"); // 获得连接 conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc"); // 获得执行SQL语句的对象: stmt = conn.createStatement(); // 编写SQL: String sql = "delete from user where uid = 4"; // 执行SQL: int i = stmt.executeUpdate(sql); if(i > 0){ System.out.println("删除成功!"); } }catch(Exception e){ e.printStackTrace(); }finally{ // 释放资源 if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } }
-
向数据库中更改记录
public void demo2(){ Connection conn = null; Statement stmt = null; try{ // 注册驱动 Class.forName("com.mysql.jdbc.Driver"); // 获得连接 conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc"); // 创建执行SQL语句的对象: stmt = conn.createStatement(); // 编写SQL: String sql = "update user set username = 'qqq',password='456' , name='赵六' where uid = 4"; // 执行SQL: int i = stmt.executeUpdate(sql); if(i>0){ System.out.println("修改成功!"); } }catch(Exception e){ e.printStackTrace(); }finally{ // 释放资源 if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } }
-
向数据库中查询记录
public void demo4(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; try{ // 注册驱动: Class.forName("com.mysql.jdbc.Driver"); // 获得连接: conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc"); // 创建执行SQL语句的对象: stmt = conn.createStatement(); // 编写SQL: String sql = "select * from user"; // 执行SQL: rs = stmt.executeQuery(sql); // 遍历结果集: while(rs.next()){ System.out.println(rs.getInt("uid")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getString("name")); } }catch(Exception e){ e.printStackTrace(); }finally{ // 释放资源 if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } } public void demo5(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; try{ // 注册驱动 Class.forName("com.mysql.jdbc.Driver"); // 获得连接 conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "abc"); // 创建执行SQL语句的对象 stmt = conn.createStatement(); // 编写SQL String sql = "select * from user where uid = 1"; // 执行SQL rs = stmt.executeQuery(sql); if(rs.next()){ System.out.println(rs.getInt("uid")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getString("name")); } }catch(Exception e){ e.printStackTrace(); }finally{ // 释放资源 if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if(stmt != null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } stmt = null; } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } } }
二、使用PreparedStatement
使用了工具类,对加载驱动、创建连接、释放资源进行了抽取,详细在《JDBC》工具类抽取博客有讲
package com.imooc.jdbc.demo2;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.imooc.jdbc.utils.JDBCUtils;
/**
* PreparedStatement的使用
* @author jt
*
*/
public class JDBCDemo5 {
@Test
/**
* 查询一条记录
*/
public void demo5(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
// 编写SQL:
String sql = "select * from user where uid = ?";
// 预编译SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数:
pstmt.setObject(1, 3);
// 执行SQL:
rs = pstmt.executeQuery();
// 判断结果集:
if(rs.next()){
System.out.println(rs.getInt("uid")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
}
@Test
/**
* 查询所有数据
*/
public void demo4(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
// 编写SQL:
String sql = "select * from user";
// 预编译SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数
// 执行SQL:
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("uid")+" "+rs.getString("username")+" "+rs.getString("password")+" "+rs.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
}
@Test
/**
* 删除数据
*/
public void demo3(){
Connection conn = null;
PreparedStatement pstmt = null;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
// 编写SQL:
String sql = "delete from user where uid = ?";
// 预编译SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数:
pstmt.setInt(1, 6);
// 执行SQL:
int num = pstmt.executeUpdate();
if(num > 0){
System.out.println("删除成功!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(pstmt, conn);
}
}
@Test
/**
* 修改数据
*/
public void demo2(){
Connection conn = null;
PreparedStatement pstmt = null;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
// 编写SQL:
String sql = "update user set username = ?,password = ?,name = ? where uid = ?";
// 预编译SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数:
pstmt.setString(1, "www");
pstmt.setString(2, "123456");
pstmt.setString(3, "张六");
pstmt.setInt(4, 6);
// 执行SQL:
int num = pstmt.executeUpdate();
if(num > 0){
System.out.println("修改成功!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(pstmt, conn);
}
}
@Test
/**
* 保存数据
*/
public void demo1(){
Connection conn = null;
PreparedStatement pstmt = null;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
// 编写SQL:
String sql = "insert into user values (null,?,?,?)";
// 预处理SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数的值:
pstmt.setString(1, "qqq");
pstmt.setString(2, "123");
pstmt.setString(3, "张武");
// 执行SQL:
int num = pstmt.executeUpdate();
if(num > 0){
System.out.println("保存成功!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
// 释放资源
JDBCUtils.release(pstmt, conn);
}
}
}
重点:所有变量用占位符占位,先预编译SQL,然后执行SQL