Statement and Preparestatement
The Bookphone database has been established in SQL Server, contains the Bookphone table, there are bookphone classes in Eclipse, three string-type values
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 ("Wang Damao", "male", "14");
Phonebook pb2=New phonebook ("Wang Xiao", "male", "14");
Insert (PB1);
System. out. println (requestbyname("Wang Damao"));
Delete ("Wang Damao");
}
}
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= "Li Guo";
String sex= "Male";
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 ("Lee Puppy Egg", "male", "12");
Insert ();
Update (Pb, "Li Guo");
Delete ("barry55");
Request ();
}
}
Two common methods for JDBC to SQL Server