java 数据库程序设计 学习笔记

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;}}