1.为了避免重新输入整条指令,可以把指令保存到test.sql,然后source test.sql来运行该脚本文件
2.create table Course(…………);
drop table Course;
3.Driver,Connection,Statement,ResultSet
使用Driver接口加载到一个合适的驱动程序,使用Connection接口连接到数据库,使用Statement接口创建和执行sql语句,如果语句返回结果的话,使用ResultSet接口处理结果
import java.sql.*;
/*
* 数据库辅助类
*/
public class DBOperation {
private static final String DBDRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/coursesystem?user=root&password=admin";
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;
public DBOperation() throws Exception {
// 连接
Class.forName(DBDRIVER);
conn = DriverManager.getConnection(URL);
stmt = conn.createStatement();
}
// 查询
public ResultSet Query(String sql) {
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
}
return rs;
}
// 更新、删除
public void TheAll(String sql) {
try {
stmt.executeUpdate(sql);
} catch (SQLException e) {
}
}
/**
* 关闭数据库链接模块
*/
public void CloseAll() {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ac) {
ac.printStackTrace();
}
}
/**
* 获取记录集总数模块
*/
public int getTotalRow(String sql) throws Exception {// 获取记录集总数
DBOperation dbo = new DBOperation();
ResultSet rsline = dbo.Query(sql);
int i = 0;
try {
while (rsline.next()) {
i++;
}
dbo.CloseAll();
} catch (Exception er) {
}
return i;
}
/**
* 获取主键最大值,然后加“1”模块
*/
public String getMax(String table, String IDItem) throws Exception {
// 生成根据ID项从高到底排序的查询语句
DBOperation dbo = new DBOperation();
String sql = "select " + IDItem + " from " + table
+ " order by " + IDItem + " desc";
String id, i = null;
try {
// 得到结果集
ResultSet rs = dbo.Query(sql);
if (rs.next()) {
// 如果数据库非空,得到第一条记录,也就是ID值最大的记录
id = rs.getString(IDItem);
// ID值增加1,得到新ID值
i = (Integer.parseInt(id) + 1) + "";
}
/*
* 根据表,自动判断插入的初始值
*/
else {
if (table == "Student") {
i = "10000";
} else if (table == "Teacher") {
i = "1000";
} else if (table == "Course") {
i = "100";
}
}
dbo.CloseAll();
} catch (Exception e) {
}
return i;
}
}