driver为JDBC的驱动。
url为数据库的地址。
usrname和password分别为数据库的用户名和密码。
Connection类用来连接MySQL。
PreparedStatement类用来预编译sql语句,并可以执行。有的地方也用Statement。因为PreparedStatement继承了Statement,并且在执行多次查询更新的时候比Statement更快。PreparedStatement的第一次执行消耗是很高的,它的性能体现在重复执行。比如当我使用PreparedStatement来进行查询的时候,JDBC会发送一个网络请求来编译这个查询语句,在执行时会产生另外一个网络请求来执行。但是当我执行多次相同的查询的时候,使用PreparedStatement只会产生一个网络请求来执行语句。但如果使用Statement还会产生一个网络请求来编译语句。
ResultSet用来存储结果,一般用到select语句时用。
import java.sql.*;
class MySQL {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost:3306/school";
private static final String usrname = "root";
private static final String password = "";
private Connection con = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
//创建student表
public MySQL() {
//如果不存在Student表则创建
String sql = "create table if not exists Student(name char(10), "
+ "sno char(10) primary key, age smallint, sex char(6), "
+ "sdept char(4))";
//加载驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);//连接数据库
ps = con.prepareStatement(sql);//预编译sql语句
ps.executeUpdate();//执行sql语句更新
//必须按顺序关闭并且必须关闭
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//插入多条信息
public void addInfo(String[] name, String[] sno, int[] age, String[] sex,
String[] sdept) {
String sql = "insert into student(name, sno, age, sex, sdept) "
+ "values(?, ?, ?, ?, ?);";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);//只需要预编译一次
for (int i = 0; i < name.length; i++) {
ps.setString(1, name[i]);
ps.setString(2, sno[i]);
ps.setInt(3, age[i]);
ps.setString(4, sex[i]);
ps.setString(5, sdept[i]);
ps.executeUpdate();
}
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//删除多条信息
public void deleteInfo(String[] name) {
String sql = "delete from student where name = ? ;";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);
for (int i = 0; i < name.length; i++) {
ps.setString(1, name[i]);
ps.executeUpdate();
}
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//查询多条信息
public void searchInfo(String[] name) {
String sql = "select * from student where name like ? ;";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);
for (int i = 0; i < name.length; i++) {
ps.setString(1, name[i]);
rs = ps.executeQuery();//返回的查询结果存在结果集里面
if (rs.next()) {
do {
System.out.println("Name: " + rs.getString(1) + ", Sno: "
+ rs.getString(2) + ", age:" + rs.getString(3)
+ ", sex: " + rs.getString(4) + ", sdept: "
+ rs.getString(5));
} while (rs.next());
}
else {
System.out.println("There is no one named " + name[i] + ".");
}
}
try {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public class TestJDBC {
static String[] name = {"Mike", "Tom", "Jack", "Amy", "Allen"};
static String[] sno = {"2013210001", "2013210002", "2013210003", "2013210004", "2013210005", };
static int[] age = {21, 22, 25, 20, 24};
static String[] sex = {"female", "female", "female", "male", "male"};
static String[] sdept = {"CS", "EE", "SE", "CS", "SE"};
public static void main(String[] args) {
//创建Student表
System.out.println("Creating table student!");
MySQL sq = new MySQL();
//添加学生信息
System.out.println("\nTesting add data!");
sq.addInfo(name, sno, age, sex, sdept);
//删除Tom和Allen的信息
System.out.println("\nTesting delete data!");
String[] del = {"Tom", "Allen"};
sq.deleteInfo(del);
//查询Tom,Amy和Mike的信息。
System.out.println("\nTesting search data!");
String[] sear = {"Tom", "Amy", "Mike"};
sq.searchInfo(sear);
System.out.println("\nSuccess!");
}
}
上述代码运行结果如下
Creating table student!
Testing add data!
Testing delete data!
Testing search data!
There is no one named Tom.//因为上一部已经将Tom删除
Name: Amy, Sno: 2013210004, age:20, sex: male, sdept: CS
Name: Mike, Sno: 2013210001, age:21, sex: female, sdept: CS
Success!