package com.imooc.jdbc.utils;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

public class JDBCUtils {

private static final String driverClass;

private static final String url;

private static final String username;

private static final String password;

static {

Properties props = new Properties();

InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");

try {

props.load(is);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

driverClass = props.getProperty("driverClass");

url = props.getProperty("url");

username = props.getProperty("username");

password = props.getProperty("password");

}

/**

* 注册驱动

* @throws ClassNotFoundException

*/

public static void loadDriver() throws ClassNotFoundException {

Class.forName(driverClass);

}

/**

* 建立连接

* @return

* @throws Exception

*/

public static Connection getConnection() throws Exception {

loadDriver();

Connection conn = DriverManager.getConnection(url, username, password);

return conn;

}

/**

* 释放资源

* @param stmt

* @param conn

*/

public static void release(Statement stmt,Connection conn) {

if(stmt != null) {

try {

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt = null;

}

if(conn != null) {

try {

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn = null;

}

}

/**

* 释放资源

* @param rs

* @param stmt

* @param conn

*/

public static void release(ResultSet rs,Statement stmt,Connection conn) {

if(rs != null) {

try {

rs.close();

}catch(SQLException e) {

e.printStackTrace();

}

rs = null;

}

if(stmt != null) {

try {

stmt.close();

}catch(SQLException e) {

e.printStackTrace();

}

stmt = null;

}

if(conn != null) {

try {

conn.close();

}catch(SQLException e) {

e.printStackTrace();

}

conn = null;

}

}

}

package com.imooc.jdbc.demo1;

import java.sql.Connection;

import java.sql.Date;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import org.junit.Test;

import com.imooc.jdbc.utils.JDBCUtils;

public class Course {

@Test

/**

*   1、首先将表格中的三条数据添加到数据库

*/

public void demo() {

Connection conn = null;

PreparedStatement pstmt = null;

String sql = null;

try {

conn = JDBCUtils.getConnection();

sql = "insert into course values(null,?,?,?,?),(null,?,?,?,?),(null,?,?,?,?)";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "Java零基础");

pstmt.setString(2, "Java");

pstmt.setString(3, "Java基础语法");

pstmt.setDate(4, new Date(System.currentTimeMillis()));

pstmt.setString(5, "Java Web");

pstmt.setString(6, "Java");

pstmt.setString(7, "Jsp和Servlet");

pstmt.setDate(8, new Date(System.currentTimeMillis()));

pstmt.setString(9, "前端小白");

pstmt.setString(10, "前端");

pstmt.setString(11, "HTML/CSS/JS");

pstmt.setDate(12, new Date(System.currentTimeMillis()));

int num = pstmt.executeUpdate();

if(num > 0) {

System.out.println("插入成功");

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(pstmt, conn);

}

}

@Test

/**

*   2、显示所有数据

*/

public void demo2() {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet resultSet = null;

String sql = null;

try {

conn = JDBCUtils.getConnection();

sql = "select * from course";

pstmt = conn.prepareStatement(sql);

resultSet = pstmt.executeQuery();

while(resultSet.next()) {

System.out.println(resultSet.getInt("id")+"        "+resultSet.getString("NAME")+"        "+resultSet.getString("category")+"        "+resultSet.getString("desp")+"        "+resultSet.getDate("createTime"));

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, pstmt, conn);

}

}

@Test

/**

*    3、查询课程名称为Java零基础的数据并显示

*/

public void demo3() {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet resultSet = null;

String sql = null;

try {

conn = JDBCUtils.getConnection();

sql = "select * from course where NAME=?";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "Java零基础");

resultSet = pstmt.executeQuery();

while(resultSet.next()) {

System.out.println(resultSet.getInt("id")+"        "+resultSet.getString("NAME")+"        "+resultSet.getString("category")+"        "+resultSet.getString("desp")+"        "+resultSet.getDate("createTime"));

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, pstmt, conn);

}

}

@Test

/**

*       4、将Java零基础的课程描述改为Java语法

*/

public void demo4() {

Connection conn = null;

PreparedStatement pstmt = null;

String sql = null;

try {

conn = JDBCUtils.getConnection();

sql = "update course set NAME=? where NAME= 'Java零基础'";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "Java语法");

int num = pstmt.executeUpdate();

if(num > 0) {

System.out.println("修改成功！");

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(pstmt, conn);

}

}

@Test

/**

*       5、删除Java Web这门课程

*/

public void demo5() {

Connection conn = null;

PreparedStatement pstmt = null;

String sql = null;

try {

conn = JDBCUtils.getConnection();

sql = "delete from course where NAME=?";

pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "Java Web");

int num = pstmt.executeUpdate();

if(num > 0) {

System.out.println("删除成功！");

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(pstmt, conn);

}

}

@Test

/**

*     6、按创建时间倒序排序显示所有数据

*/

public void demo6() {

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet resultSet = null;

String sql = null;

try {

conn = JDBCUtils.getConnection();

sql = "select * from course order by createTime desc";

pstmt = conn.prepareStatement(sql);

resultSet = pstmt.executeQuery();

while(resultSet.next()) {

System.out.println(resultSet.getInt("id")+"        "+resultSet.getString("NAME")+"        "+resultSet.getString("category")+"        "+resultSet.getString("desp")+"        "+resultSet.getDate("createTime"));

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, pstmt, conn);

}

}

}