package com.zj.jdbc.demo;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import org.junit.jupiter.api.Test;

import com.zj.jdbc.utils.JDBCUtils;

public class CRUD {

//插入所有数据

@Test

public void insert() {

Connection connection = null;

Statement statement = null;

try {

connection = JDBCUtils.getConnection();

statement = connection.createStatement();

String sqlString = "insert goods values(null,'手机',2000,'黑色，存储容量32G'),"

+ "(null,'冰箱',1500,'银色，对开门'),(null,'洗衣机',3000,'滚筒'),"

+ "(null,'空调',4000,'变频空调')";

int i = statement.executeUpdate(sqlString);

if (i>0) {

System.out.println("success");

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(statement, connection);

}

}

//查看所有数据

@Test

public void search() {

Connection connection = null;

Statement statement =null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

statement = connection.createStatement();

String sqlString = "select * from goods";

resultSet = statement.executeQuery(sqlString);

while (resultSet.next()) {

System.out.println(resultSet.getInt("id")+" "+resultSet.getString("name")+" "+resultSet.getFloat("price")+" "+resultSet.getString("desp"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(statement, connection, resultSet);

}

}

//获取冰箱数据

@Test

public void searchByName() {

Connection connection = null;

Statement statement =null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

statement = connection.createStatement();

String sqlString = "select * from goods where name = '冰箱'";

resultSet = statement.executeQuery(sqlString);

while (resultSet.next()) {

System.out.println(resultSet.getInt("id")+" "+resultSet.getString("name")+" "+resultSet.getFloat("price")+" "+resultSet.getString("desp"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(statement, connection, resultSet);

}

}

//更新手机价格

@Test

public void updateByName() {

Connection connection = null;

Statement statement =null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

statement = connection.createStatement();

String sqlString = "update goods set price = 5000 where name = '手机'";

int i = statement.executeUpdate(sqlString);

if (i>0) {

System.out.println("success");

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(statement, connection, resultSet);

}

}

//删除洗衣机数据

@Test

public void deleteByName() {

Connection connection = null;

Statement statement =null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

statement = connection.createStatement();

String sqlString = "delete from goods where name = '洗衣机'";

int i = statement.executeUpdate(sqlString);

if (i>0) {

System.out.println("success");

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(statement, connection, resultSet);

}

}

//按价格升序显示数据

@Test

public void searchByAsc() {

Connection connection = null;

Statement statement =null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

statement = connection.createStatement();

String sqlString = "select * from goods order by price asc";

resultSet = statement.executeQuery(sqlString);

while (resultSet.next()) {

System.out.println(resultSet.getInt("id")+" "+resultSet.getString("name")+" "+resultSet.getFloat("price")+" "+resultSet.getString("desp"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(statement, connection, resultSet);

}

}

// //插入数据

// @Test

// public void insert() {

// Connection connection = null;

// Statement statement = null;

// try {

// Class.forName("com.mysql.jdbc.Driver");

// connection = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test"+"?useUnicode=true&characterEncoding=utf-8","root","root");

// statement = (Statement) connection.createStatement();

// String sqlString = "insert goods(id,name,price,desp) values(null,'耳机',200,'蓝牙耳机')";

// int i = statement.executeUpdate(sqlString);

// if (i>0) {

// System.out.println("success!");

// }

// } catch (Exception e) {

// e.printStackTrace();

// } finally {

// if (connection != null) {

// try {

// connection.close();

// } catch (SQLException e1) {

// e1.printStackTrace();

// }

// connection=null;

// }

// if (statement != null) {

// try {

// statement.close();

// } catch (SQLException e1) {

// e1.printStackTrace();

// }

// statement=null;

// }

// }

// }

}