1 未预编译的增删改查
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. ResultSet;
import java. sql. Statement;
public class DML_DQL1test {
static void add ( ) {
Connection con = null;
Statement st = null;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
con = DriverManager. getConnection ( "jdbc:mysql://192.168.16.241:3306/mytest" , "root" , "root" ) ;
st = con. createStatement ( ) ;
int i = st. executeUpdate ( "insert into student(sname,gender,classid) values('赵云','女','3')" ) ;
if ( i> 0 ) {
System. out. println ( "成功" ) ;
} else {
System. out. println ( "失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
if ( st != null)
st. close ( ) ;
if ( con != null)
con. close ( ) ;
} catch ( Exception e2) {
e2. printStackTrace ( ) ;
}
}
}
static void select ( ) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
con = DriverManager. getConnection ( "jdbc:mysql://192.168.16.241:3306/mytest" , "root" , "root" ) ;
st = con. createStatement ( ) ;
rs = st. executeQuery ( "select * from student where sid=5" ) ;
if ( rs. next ( ) ) {
System. out. print ( "编号:" + rs. getInt ( "sid" ) + " " ) ;
System. out. print ( "姓名:" + rs. getString ( "sname" ) + " " ) ;
System. out. print ( "性别:" + rs. getString ( "gender" ) + " " ) ;
System. out. print ( "班级:" + rs. getInt ( "classid" ) + " " ) ;
System. out. print ( "分数:" + rs. getInt ( "score" ) + " " ) ;
System. out. println ( "课程:" + rs. getString ( "cusion" ) + " " ) ;
}
rs = st. executeQuery ( "select sname as aa from student where sid=5" ) ;
if ( rs. next ( ) ) {
System. out. println ( "姓名:" + rs. getString ( "aa" ) ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
if ( rs != null)
rs. close ( ) ;
if ( st != null)
st. close ( ) ;
if ( con != null)
con. close ( ) ;
} catch ( Exception e2) {
e2. printStackTrace ( ) ;
}
}
}
public static void main ( String[ ] args) {
add ( ) ;
select ( ) ;
}
}
2 有预编译的增删改查
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. PreparedStatement;
import java. sql. ResultSet;
public class DML_DQL2test {
static void update ( ) {
Connection con = null;
PreparedStatement pst = null;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
con = DriverManager. getConnection ( "jdbc:mysql://192.168.16.241:3306/mytest" , "root" , "root" ) ;
pst = con. prepareStatement ( "insert into student(sname,gender,classid) values(?,?,?)" ) ;
pst. setString ( 1 , "小黄第" ) ;
pst. setString ( 2 , "男" ) ;
pst. setInt ( 3 , 5 ) ;
int i = pst. executeUpdate ( ) ;
System. out. println ( i> 0 ? "成功" : "失败" ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
if ( pst != null)
pst. close ( ) ;
if ( con != null)
con. close ( ) ;
} catch ( Exception e2) {
e2. printStackTrace ( ) ;
}
}
}
static void select ( ) {
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
con = DriverManager. getConnection ( "jdbc:mysql://192.168.16.241:3306/mytest" , "root" , "root" ) ;
pst = con. prepareStatement ( "select * from student " ) ;
rs = pst. executeQuery ( ) ;
while ( rs. next ( ) ) {
System. out. print ( "编号:" + rs. getInt ( "sid" ) + " " ) ;
System. out. print ( "姓名:" + rs. getString ( "sname" ) + " " ) ;
System. out. print ( "性别:" + rs. getString ( "gender" ) + " " ) ;
System. out. print ( "班级:" + rs. getInt ( "classid" ) + " " ) ;
System. out. print ( "分数:" + rs. getInt ( "score" ) + " " ) ;
System. out. println ( "课程:" + rs. getString ( "cusion" ) + " " ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
try {
if ( pst != null)
pst. close ( ) ;
if ( con != null)
con. close ( ) ;
} catch ( Exception e2) {
e2. printStackTrace ( ) ;
}
}
}
public static void main ( String[ ] args) {
select ( ) ;
}
}
3 封装
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. PreparedStatement;
import java. sql. ResultSet;
public class myDB {
public static final String driver= "com.mysql.jdbc.Driver" ;
public static final String url= "jdbc:mysql://192.168.9.171:3306/test" ;
public static final String user= "root" ;
public static final String password= "root" ;
Connection co= null;
PreparedStatement pst= null;
ResultSet rs= null;
public Connection getco ( ) {
try {
Class. forName ( driver) ;
co= DriverManager. getConnection ( url, user, password) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
return co;
}
public void closeAll ( ) {
try {
if ( rs!= null) rs. close ( ) ;
if ( pst!= null) pst. close ( ) ;
if ( co!= null) co. close ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
}
public int update ( String sql, Object. . . obj) {
int result= 0 ;
try {
co= getco ( ) ;
pst= co. prepareStatement ( sql) ;
if ( obj!= null) {
for ( int i= 0 ; i< obj. length; i++ ) {
pst. setObject ( i+ 1 , obj[ i] ) ;
}
}
result= pst. executeUpdate ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
closeAll ( ) ;
}
return result;
}
public ResultSet select ( String sql, Object. . . obj) {
try {
co= getco ( ) ;
pst= co. prepareStatement ( sql) ;
if ( obj!= null) {
for ( int i= 0 ; i< obj. length; i++ ) {
pst. setObject ( i+ 1 , obj[ i] ) ;
}
}
rs= pst. executeQuery ( ) ;
} catch ( Exception e) {
e. printStackTrace ( ) ;
}
return rs;
}
}
import java. sql. ResultSet;
public class test {
public static void main ( String[ ] args) {
myDB db = new myDB ( ) ;
int i1= db. update ( "insert into student(name,gender,score) value (?,?,?)" , "小黑" , "女" , 75 ) ;
System. out. println ( i1> 0 ? "成功" : "失败" ) ;
int i2= db. update ( "update student set name=? where id=?" , "小白" , 1201232065 ) ;
System. out. println ( i2> 0 ? "成功" : "失败" ) ;
int i3= db. update ( "delete from student where id=? or id=?" , 1 , 2 ) ;
System. out. println ( i3> 0 ? "成功" : "失败" ) ;
try {
ResultSet rs = db. select ( "select * from student where id=?" , 1201232064 ) ;
while ( rs. next ( ) ) {
System. out. println ( rs. getString ( "name" ) + " " + rs. getInt ( "score" ) ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
db. closeAll ( ) ;
}
}
}