1.SQL语句
1.1通过Connection对象创建语句对象,对应如下
public Statement createStatement() throws SQLException
创建语句对象之后,可以调用语句对象的executeUpdate方法执行对数据库进行更新的语句
public int executeUpdate(String sql) throws SQLException
返回值表示成功的操作了多少条数据库记录
可以调用executeQuery方法执行对数据库的查询
public ResultSet executeQuery(String sql) throws SQLException
1.2ResultSet
数据库查询操作返回的结果集,在提取封装在ResultSet中的记录时,涉及三类操作
a.设置提取方法以及对ResultSet中游标的操作
b.得到记录中的数据或删除或更新记录操作
c.得到有关数据表信息,即元数据的操作
注意:ResultSet预设的游标位置为0。首次调用next()时,游标位置为第一个记录的开始。也就是说不调用next()方法,将会显示空结果集(无论实际上结果集是否为空)。
ResultSet常用方法,其中getxxx()都有两个重载的方法,其中一个接受int类型参数,参数值为要获取值的字段对应的列索引。另一个接受String类型参数,参数值为要获取值的字段对应的列名称
方法名 | 返回类型 | 方法名 | 返回类型 |
---|---|---|---|
getDouble | double | getByte | byte |
getInt | int | getBytes | byte[] |
getFloat | float | getDate | java.sql.Date |
getString | String | getTime | java.sql.Time |
getObject | Object | getBoolean | boolean |
具体实例
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class StatementTest {
private Connection con = null;
public StatementTest(String url, String user, String password) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void select() throws SQLException {
Statement ste = con.createStatement();
ResultSet rs = ste.executeQuery("select * from student");
while(rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " "
+ rs.getString("sno") + " " + rs.getString("sname"));//对应数据库表每列
}
}
public void delete() throws SQLException {
Statement ste = con.createStatement();
int row = ste.executeUpdate("delete from student where sno = '22150003'");
if(row > 0) {
System.out.println("删除成功");
}
}
public void insert() throws SQLException{
Statement ste = con.createStatement();
int row = ste.executeUpdate("insert into student( sno, sname, sage ) values ('22150003', '刘邦', 20)");
if(row > 0) {
System.out.println("添加成功");
}
}
public void update() throws SQLException{
Statement ste = con.createStatement();
int row = ste.executeUpdate("update student set sname = 'helloworld' where sno = '22170001'");
if(row > 0) {
System.out.println("修改成功");
}
}
public static void main(String[] args) throws SQLException {
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=student";//注意修改DatabaseName
String user = "sa";
String password = "123456";
StatementTest ste = new StatementTest(url, user, password);
ste.delete();
ste.insert();
ste.update();
ste.select();
}
}
2.预编译
Java通过java.sql.PreparedStatement 来执行指定的SQL语句,PreparedStatement 继承自Statement,因此不但具有Statement的功能而且具备许多新能力。与Statement类似,PreparedStatement 语句对象也是通过Connection的方法来创建
public PreparedStatement PreparedStatement (String sql) throws SQLException
从这里可以看出PreparedStatement 与Statement的区别,他是在创建语句对象时给出要执行的SQL语句。这样,SQL语句会被系统进行预编译,执行的时候速度会有所增加。
2.1新功能
PreparedStatement 还支持占位符功能,即参数可以不用在创建PreparedStatement 语句对象时给出,可以在后期操作中传递具体参数
PreparedStatement 占位符功能相关方法
方法名 | 参数序列 | 方法名 | 参数序列 |
---|---|---|---|
setBoolean | int, boolean | setByte | int, byte |
setBytes | int, byte[] | setDate | int, java.sql.Date |
setDouble | int, double | setFloat | int, float |
setInt | int, int | setLong | int, long |
setObject | int, Object | setString | int, String |
setSgirt | int, short |
具体实例
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PreparedStatementTest implements Function{
private Connection con = null;
private PreparedStatement preSte = null;
public PreparedStatementTest(String url, String user, String password) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void select(String sno) throws SQLException {
preSte = con.prepareStatement("select * from student where sno = ?");
preSte.setString(1, sno);
ResultSet rs = preSte.executeQuery();
while(rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2) + " "
+ rs.getString("sno") + " " + rs.getString("sname"));//对应数据库表每列
}
}
@Override
public void insert(String sno, String sname) throws SQLException {
preSte = con.prepareStatement("insert into student(sno, sname) values(?, ?)");
preSte.setString(1, sno);
preSte.setString(2, sname);
int row = preSte.executeUpdate();
if(row > 0) {
System.out.println("添加成功");
}
}
@Override
public void update(String sno, String sname) throws SQLException {
preSte = con.prepareStatement("update student set sname = ? where sno = ?");
preSte.setString(1, sname);
preSte.setString(2, sno);
int row = preSte.executeUpdate();
if(row > 0) {
System.out.println("修改成功");
}
}
@Override
public void delete(String sno) throws SQLException {
preSte = con.prepareStatement("delete from student where sno = ?");
preSte.setString(1, sno);
int row = preSte.executeUpdate();
if(row > 0) {
System.out.println("删除成功");
}
}
public static void main(String[] args) throws SQLException {
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=student";//注意修改DatabaseName
String user = "sa";
String password = "123456";
PreparedStatementTest test = new PreparedStatementTest(url, user, password);
test.select("22170001");
test.insert("22170002", "A1");
test.update("22170002", "B2");
test.delete("22170002");
}
}