JDBC中的CRUD与SQL的关系
JDBC对数据库的增删改查是JDBC技术的基础，是以后学习 事务，批处理，可更新结果等技术的奠基石。使用JDBC与数据库进行连接的根本目的就是操作数据，然而对数据的操作就是增，删，改，查，简称“CRUD”。对数据库的增删改查的基础是SQL技术，利用java程序将sql语句送至数据库，让数据库管理系统对语句进行解析，然后返回相应的结果。
数据库的结构
则部分毋庸多言，数据库中的数据是以二维表的形式存在的，如图所示：
SQL简介
SQL（结构化查询语言）分为DML（数据库操作语言）和DDL（数据库定义语言）。
DML包括以下四部分：
SELECT -----查询语句
INSERT INTO -----插入语句< http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vc3Ryb25nPjwvcD4KPHA+PHN0cm9uZz5ERUxFVEUgIC0tLS0tyb6z/dPvvuQ8L3N0cm9uZz48L3A+CjxwPjxzdHJvbmc+VVBEQVRFIC0tLS0tuPzQwtPvvuQ8L3N0cm9uZz48L3A+CjxwPkRETLD8wKjS1M/Csr+31qO6PGVtPjxicj4KPC9lbT48L3A+CjxwPjxzdHJvbmc+Q1JFQVRFIERBVEFCQVNFIC0gtLS9qNDCyv2+3b/iPGVtPjxicj4KPC9lbT48L3N0cm9uZz48L3A+CjxwPjxzdHJvbmc+QUxURVIgREFUQUJBU0U8ZW0+PC9lbT4tINDeuMTK/b7dv+I8ZW0+PGJyPgo8L2VtPjwvc3Ryb25nPjwvcD4KPHA+PHN0cm9uZz5DUkVBVEUgVEFCTEUgLSC0tL2o0MKx7TxlbT48YnI+CjwvZW0+PC9zdHJvbmc+PC9wPgo8cD48c3Ryb25nPkFMVEVSIFRBQkxFIC0gseS4/KOouMSx5KOpyv2+3b/ise08ZW0+PGJyPgo8L2VtPjwvc3Ryb25nPjwvcD4KPHA+PHN0cm9uZz5EUk9QIFRBQkxFIC0gyb6z/bHtPGVtPjxicj4KPC9lbT48L3N0cm9uZz48L3A+CjxwPjxzdHJvbmc+Q1JFQVRFIElOREVYIC0gtLS9qMv30v2jqMvRy/e8/KOpPGVtPjxicj4KPC9lbT48L3N0cm9uZz48L3A+CjxwPjxzdHJvbmc+RFJPUCBJTkRFWCAtIMm+s/3L99L9PC9zdHJvbmc+PC9wPgo8aDI+PHN0cm9uZz7K/b7dv+K1xNT2yb64xLLpPC9zdHJvbmc+PC9oMj4KPGgzPklOU0VSVKGhSU5UT9PvvuQ8L2gzPgo8cD6wstewbXlzcWzK/b7dv+KjrMq508PK/b7dv+K/zbuntsu0tL2o0ru49lBlcnNvbnO1xMr9vt2/4qOs1Nq4w8r9vt2/4tbQtLS9qNK7uPZQZXJzb261xMr9vt2x7aGj08PAtLTmt8XK/b7doaPU2lBlcnNvbtbQsuXI57y40NDK/b7doaO0+sLryr7A/aO6PC9wPgo8cD48L3A+CjxwcmUgY2xhc3M9"brush:java;">package com.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import com.mysql.jdbc.Driver; public class Test2 { /** * 用两种方法插入数据 */ public static void main(String[] args) { try { insertTest_2(); }catch(SQLException e) { e.printStackTrace(); } } //没有使用预编译语句 public static void insertTest_1() throws SQLException { //1,注册驱动 DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //2,获得链接 Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/Persons", "root", "root"); //3,创建语句 Statement stmt=conn.createStatement(); String sql="insert into Person values (1,'Adams','John','Oxford Street','London')"; //4,执行语句 int n=stmt.executeUpdate(sql); if(n>0) { System.out.println("插入成功"); }else { System.out.println("插入失败"); } conn.close(); stmt.close(); } //使用预编译语句：可以有效的提高效率，放置sql注入。 public static void insertTest_2() throws SQLException { Connection conn =getConnection(); String sql ="insert into Person values ( , , , , )"; PreparedStatement stmt=conn.prepareStatement(sql); stmt.setInt(1, 4); stmt.setString(2, "Wang"); stmt.setString(3, "Wu"); stmt.setString(4, "Yushang Road"); stmt.setString(5, "Shanghai"); int n=stmt.executeUpdate(); if(n>0) { System.out.println("插入成功"); }else { System.out.println("插入失败"); } } //在做第二个方法的时候，将获取Connection的代码提取出来 public static Connection getConnection() { Connection conn=null; try { DriverManager.registerDriver(new com.mysql.jdbc.Driver()); conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/Persons","root","root"); }catch(SQLException e) { e.printStackTrace(); } return conn; } }
在开发中一般使用预编译语句，以提高效率。
SELECT语句
首先创建一个Persons的数据库。代码示例
package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/*
* 获取数据库中的名字
*
*/
public class Test1 {
public static void main(String[] args) {
test();
}
static void test()
{
//1,注册驱动
try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//2,建立连接
Connection conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/Persons","root","root");
//3，创建语句
Statement stmt = conn.createStatement();
//4,执行查询
ResultSet rs = stmt.executeQuery("select LastName from Person");
//5,处理结果
while(rs.next())
{
System.out.println(rs.getString("LastName"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
} DELETE 语句
代码示例：
package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test4 {
/**
* 删除掉所有城市是上海的记录
*/
public static void main(String[] args) throws SQLException {
Connection conn=getConn();
Statement stmt=conn.createStatement();
int n=stmt.executeUpdate("delete from Person where City='Shanghai' ");
if(n>0)
{
System.out.println("共有"+n+"条数据被删除");
}
}
public static Connection getConn()
{
Connection conn=null;
try
{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/Persons", "root", "root");
}catch(SQLException e)
{
e.printStackTrace();
}
return conn;
}
}
UPDATE语句：
package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test5 {
/**
* 更改某一列的值
*/
public static void main(String[] args) throws SQLException{
//1，注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//2，获得链接
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/Persons","root","root");
//获得语句
Statement stmt=conn.createStatement();
//
String sql="update Person set FirstName='David' where FirstName='John'";
//执行语句
int n=stmt.executeUpdate(sql);
if(n>0)
{
System.out.println("修改成功");
}else
{
System.out.println("修改失败");
}
conn.close();
stmt.close();
}
}
DELETE语句：
package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test4 {
/**
* 删除掉所有城市是上海的记录
*/
public static void main(String[] args) throws SQLException {
Connection conn=getConn();
Statement stmt=conn.createStatement();
int n=stmt.executeUpdate("delete from Person where City='London' ");
if(n>0)
{
System.out.println("共有"+n+"条数据被删除");
}
conn.close();
stmt.close();
}
public static Connection getConn()
{
Connection conn=null;
try
{
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/Persons", "root", "root");
}catch(SQLException e)
{
e.printStackTrace();
}
return conn;
}
}