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.ResultSet;

import java.sql.Statement;

import org.junit.Test;

import com.imooc.jdbc.utils.JDBCUtils;

public class goods {

/**

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

*/

@Test

public void demo() {

Connection conn = null;

Statement stmt = null;

try {

// 获得连接

conn = JDBCUtils.getConnection();

//创建执行SQL语句的对象

stmt = conn.createStatement();

//编写SQL

String sql = "INSERT INTO goods(NAME,price,desp) VALUES('手机',2000,'黑色，存储容量为32G'),('冰箱',1500,'银色，对开门'),('洗衣机',3000,'滚筒'),('空调',4000,'变频空调')";

int i = stmt.executeUpdate(sql);

if(i > 0) {

System.out.println("添加成功！");

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/**

* 2、显示所有数据

*/

@Test

public void demo2() {

Connection conn = null;

Statement stmt = null;

ResultSet resultSet = null;

try {

conn = JDBCUtils.getConnection();

stmt = conn.createStatement();

String sql = "select * from goods";

resultSet = stmt.executeQuery(sql);

while(resultSet.next()) {

int id = resultSet.getInt("id");

String name = resultSet.getString("NAME");

String price = resultSet.getString("price");

String desp = resultSet.getString("desp");

System.out.println(id+"           "+name+"           "+price+"           "+desp);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, stmt, conn);

}

}

/**

* 3、查询name值为冰箱的数据并显示

*/

@Test

public void demo3() {

Connection conn = null;

Statement stmt = null;

ResultSet resultSet = null;

try {

conn = JDBCUtils.getConnection();

stmt = conn.createStatement();

String sql = "select * from goods where NAME='冰箱'";

resultSet = stmt.executeQuery(sql);

while(resultSet.next()) {

int id = resultSet.getInt("id");

String name = resultSet.getString("NAME");

String price = resultSet.getString("price");

String desp = resultSet.getString("desp");

System.out.println(id+"           "+name+"           "+price+"           "+desp);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, stmt, conn);

}

}

/**

*  4、将name值为手机的数据的price值改为5000

*/

@Test

public void demo4() {

Connection conn = null;

Statement stmt = null;

ResultSet resultSet = null;

try {

conn = JDBCUtils.getConnection();

stmt = conn.createStatement();

String sql = "update goods set price=5000 where NAME='手机'";

int i = stmt.executeUpdate(sql);

if(i > 0) {

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

}

sql = "select * from goods";

resultSet = stmt.executeQuery(sql);

while(resultSet.next()) {

int id = resultSet.getInt("id");

String name = resultSet.getString("NAME");

String price = resultSet.getString("price");

String desp = resultSet.getString("desp");

System.out.println(id+"           "+name+"           "+price+"           "+desp);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, stmt, conn);

}

}

/**

*  5、删除name值为洗衣机的数据

*/

@Test

public void demo5() {

Connection conn = null;

Statement stmt = null;

ResultSet resultSet = null;

try {

conn = JDBCUtils.getConnection();

stmt = conn.createStatement();

String sql = "delete from goods where NAME='洗衣机'";

int i = stmt.executeUpdate(sql);

if(i > 0) {

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

}

sql = "select * from goods";

resultSet = stmt.executeQuery(sql);

while(resultSet.next()) {

int id = resultSet.getInt("id");

String name = resultSet.getString("NAME");

String price = resultSet.getString("price");

String desp = resultSet.getString("desp");

System.out.println(id+"           "+name+"           "+price+"           "+desp);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, stmt, conn);

}

}

/**

*  6、按价格升序排序显示所有数据

*/

@Test

public void demo6() {

Connection conn = null;

Statement stmt = null;

ResultSet resultSet = null;

try {

conn = JDBCUtils.getConnection();

stmt = conn.createStatement();

String sql = "select * from goods order by price ASC";

resultSet = stmt.executeQuery(sql);

while(resultSet.next()) {

int id = resultSet.getInt("id");

String name = resultSet.getString("NAME");

String price = resultSet.getString("price");

String desp = resultSet.getString("desp");

System.out.println(id+"           "+name+"           "+price+"           "+desp);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

JDBCUtils.release(resultSet, stmt, conn);

}

}

}