一:jdbc工具类JDBC_Tools
1.数据库连接
public static Connection getConnection() throws Exception {
// 1). 创建 Properties 对象
Properties properties = new Properties();
InputStream in = JDBC_Tools.class.getClassLoader().getResourceAsStream(
"jdbc.properties");
properties.load(in);
// 2). 具体决定 user, password 等4 个字符串.
String user = properties.getProperty("username");
String password = properties.getProperty("password");
String jdbcUrl = properties.getProperty("jdbcUrl");
String driver = properties.getProperty("driver");
// 3. 加载数据库驱动程序(对应的 Driver 实现类中有注册驱动的静态代码块.)
Class.forName(driver);
// 4. 通过 DriverManager 的 getConnection() 方法获取数据库连接.
return DriverManager.getConnection(jdbcUrl, user, password);}
数据库关闭 relaseSource
public static void relaseSource(ResultSet rs, Connection conn , Statement statement){
if(rs != null){ try {
rs.close();
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace();
} } JDBC_Tools.relaseSource(conn, statement);
} public static void relaseSource(Connection conn ,Statement statement){ if(statement!=null){ try { statement.close();
} catch (SQLException e) { e.printStackTrace();
} } //使用两个 if ,这样即使中间出现异常,程序还是继续执行下去
if(conn!=null){ try { conn.close();
} catch (SQLException e) { e.printStackTrace();
} } }
数据库操作常用方法update/insert/add...后期添加
public static int update (String sql) {
Connection conn = null; Statement statement = null; int num = 0; try { try {
conn = JDBC_Tools.getConnection(); } catch (Exception e){
e.printStackTrace(); } statement = conn.createStatement(); num = statement.executeUpdate(sql); } catch
(SQLException e){
e.printStackTrace(); }finally{
JDBC_Tools.relaseSource(conn, statement); } return num;
}
Statement: 用于执行 SQL 语句的对象
* 1). 通过 Connection 的 createStatement() 方法来获取
* 2). 通过 executeUpdate(sql) 可以执行 SQL 语句.
* 3). 传入的 SQL 可以是 INSRET, UPDATE 或 DELETE. 但不能是 SELECT
//获取连接
connection=getConnection();
//调用Connection对象的createStatement()方法获取Statement对象
statement=connection.createStatement();
//准备 SQL 语句
String sql = "UPDATE customers SET name = 'Jerry'WHERE id = 2";
//发送 SQL 语句: 调用 Statement 对象的 executeUpdate(sql) 方
statement.executeUpdate(sql);
public void testStatement1() throws Exception{
//1. 获取数据库连接
Connection conn = null;
Statement statement = null;
try {
conn = JDBC_Tools.getConnection();
//3. 准备插入/更新的 SQL 语句
String sql = null;
/*sql = "DELETE FROM customers WHERE id = 1";*/
/*sql = "UPDATE customers SET name = 'TOM' " +
"WHERE id = 4";*/
sql = "INSERT INTO Grade(name,sex,grade) " +
"VALUES('王玉', '女', '98')";
/*sql = "DELETE FROM grade WHERE id = 1";*/
/*sql="UPDATE grade SET name='马燕' WHERE id=5";*/
System.out.println(sql);
//4. 执行插入.
//1). 获取操作 SQL 语句的 Statement 对象:
//调用 Connection 的 createStatement() 方法来获取
statement = conn.createStatement();
//2). 调用 Statement 对象的 executeUpdate(sql) 执行 SQL 语句进行插入
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBC_Tools.relaseSource(null,conn,statement);
}
}
结果集ResultSet(查询)
Statement st = conn.CreateStatement
ResultSet rs = Statement.excuteQuery(sqlStr);
while(result.next()) ...{
System.out.print(result.getString(1) + " ");
System.out.print(result.getString(2) + " ");
...
}
@Test
public void testResultSet(){
//获取 id=4 的 Grade 数据表的记录, 并打印
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
//1. 获取 Connection
conn = JDBC_Tools.getConnection();
//2. 获取 Statement
statement = conn.createStatement();
//3. 准备 SQL
String sql = "SELECT id,name,sex,grade FROM grade WHERE id=4";
//4. 执行查询, 得到 ResultSet
rs = statement.executeQuery(sql);
//5. 处理 ResultSet
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
String sex = rs.getString(3);
Float grade=rs.getFloat(4);
System.out.println(id+"\t"+name+"\t"+sex+"\t"+grade);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
//6. 关闭数据库资源.
JDBC_Tools.relaseSource(rs,conn,statement);
}
}