我们在数据库study中创建了student表。
package mysqltest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
/*创建测试类*/
class Run1{
public Connection c;
public Run1() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("加载成功");
}
public void connect() {
try {
c= DriverManager.getConnection("jdbc:mysql://link.mysql.rds.aliyuncs.com:3306/study", "name", "password");
//自己的外网地址,账号密码!!!!
System.out.println("连接成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("连接失败");
e.printStackTrace();
}
}
/*
* 插入三条数据
* */
public void insert() {
String sql="insert into student values(?,?)";
PreparedStatement s=null;
Integer i=0;
String[] li= {"mary","jason","joe"};
try {
while(i<3) {
s=c.prepareStatement(sql);
s.setString(1, i.toString());
s.setString(2, li[i]);
i++;
s.execute();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
* 输出表中所有数据
* */
public void select() {
String sql="select * from student";
Statement s = null;
try {
s = c.createStatement();
ResultSet t = s.executeQuery(sql);
System.out.println("select:");
while(t.next()) {
String s1=t.getString("studentid");
String s2=t.getString("studentname");
System.out.println(s1+" "+s2);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/*
* 更新表中id数据项对应的name*/
public void update(String id,String name) {
String sql="update student set studentname=? where studentid=?";
PreparedStatement s = null;
try {
s = c.prepareStatement(sql);
System.out.println("update:");
s.setString(1, name);
s.setString(2, id);
s.execute();
System.out.println("after update\n");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/*
* 删除name的数据项*/
public void delete() {
String sql="delete from student where studentname=?";
PreparedStatement s = null;
try {
s = c.prepareStatement(sql);
System.out.println("delete:");
s.setString(1, "mary");
s.execute();
System.out.println("after delete.\n");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/*
* 关闭对象
* */
public void quit() {
try {
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public class runtest {
public static void main(String args[]) {
Run1 test=new Run1();
test.connect();
test.insert();
test.select();
test.update("1", "jackson");
test.delete();
test.select();
test.quit();
}
}
讲解:
1.Statement是用于执行SQL语句的。s.execute执行sql语句
2.PreparedStatement也是用来执行sql语句的,但是其能进行参数设置,进行预编译,速度优于statement。使用s.setString/setInteger...(parameterIndex, x);
替换String sql中的参数(?)
3.上面引入的方法都是java.sql.*,注意不要引用错了。