Statement和prepareStatement
sql server中已建立BookPhone数据库，包含bookPhone表，eclipse中有BookPhone类，三个string类型的值
1.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class jdbcDemo01 {
private final static String URL = "jdbc:sqlserver://127.0.0.1:1433;databasename=BookPhone";
private final static String user = "sa";
private final static String password = "123456";
static void insert(PhoneBook pb){
Connection conn=null;
PreparedStatement ps=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="insert into bookPhone(ph_name,ph_sex,ph_age) values(?,?,?)";
ps=conn.prepareStatement(sqlString);
ps.setString(1,pb.getName());
ps.setString(2,pb.getSex());
ps.setString(3,pb.getAge());
ps.executeUpdate();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
static void update(PhoneBook pb,String oldname){
Connection conn=null;
PreparedStatement ps=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="update bookPhone set ph_name=?,ph_sex=?,ph_age=? where ph_name=?";
ps=conn.prepareStatement(sqlString);
ps.setString(1,pb.getName());
ps.setString(2,pb.getSex());
ps.setString(3,pb.getAge());
ps.setString(4,oldname);
ps.executeUpdate();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
static void delete(String name){
Connection conn=null;
PreparedStatement ps=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="delete bookPhone where ph_name=?";
ps=conn.prepareStatement(sqlString);
ps.setString(1,name);
ps.executeUpdate();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
static PhoneBook requestByName(String name){
Connection conn=null;
PreparedStatement ps=null;
PhoneBook pb=null;
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(URL, user, password);
String sqlString="select * from bookPhone where ph_name=?";
ps=conn.prepareStatement(sqlString);
ps.setString(1,name);
ResultSet rs=ps.executeQuery();
while(rs.next()){
pb=new PhoneBook();
pb.setName(rs.getString(1));
pb.setSex(rs.getString(2));
pb.setAge(rs.getString(3));
System.out.print(rs.getString(2)+",");
System.out.print(rs.getString(3));
}
rs.close();
ps.close();
conn.close();
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch(ClassNotFoundException e){
e.printStackTrace();
}
return pb;
}
public static void main(String[] args) {
PhoneBook pb1=new PhoneBook("王大毛","男","14");
PhoneBook pb2=new PhoneBook("王小毛","男","14");
insert(pb1);
System.out.println(requestByName("王大毛"));
delete("王大毛");
}
}
2.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcDemo {
private final static String URL="jdbc:sqlserver://localhost:1433;database=BookPhone";
private final static String user="sa";
private final static String password="123456";
static void insert(){
String name="李狗蛋";
String sex="男";
String age="12";
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="insert into bookPhone(ph_name,ph_sex,ph_age)"
+"values("+"'"+name+"','"+sex+"','"+age+"')";
Statement stmt=conn.createStatement();
stmt.executeUpdate(sqlString);
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
static void update(PhoneBook pb,String oldName){
String name=pb.getName();
String sex=pb.getSex();
String age=pb.getAge();
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="update bookPhone set ph_name='"+name+"',ph_sex='"+sex+"',ph_age='"+age+"' where ph_name='"+oldName+"'";
Statement stmt=conn.createStatement();
stmt.executeUpdate(sqlString);
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
static void delete(String name){
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="delete bookPhone where ph_name='"+name+"'";
Statement stmt=conn.createStatement();
stmt.executeUpdate(sqlString);
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
static void request(){
try{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn=DriverManager.getConnection(URL,user,password);
String sqlString="select * from bookPhone";
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sqlString);
while(rs.next()){
System.out.print(rs.getString(1)+",");
System.out.print(rs.getString(2)+",");
System.out.println(rs.getString(3));
}
System.out.println();
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
public static void main(String[] args) {
PhoneBook pb=new PhoneBook("李小狗蛋","男","12");
insert();
update(pb,"李狗蛋");
delete("barry55");
request();
}
}