package com.zj.jdbc.demo;

import java.sql.Connection;

import java.sql.Date;

import java.sql.PreparedStatement;

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;

PreparedStatement preparedStatement = null;

int j;

try {

connection = JDBCUtils.getConnection();

String sqlString = "insert course values(null,?,?,?,?),(null,?,?,?,?),(null,?,?,?,?)";

preparedStatement = connection.prepareStatement(sqlString);

preparedStatement.setString(1, "java零基础");

preparedStatement.setString(2, "java");

preparedStatement.setString(3, "java基础语法");

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

preparedStatement.setString(5, "java Web");

preparedStatement.setString(6, "java");

preparedStatement.setString(7, "jsp和Servlet");

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

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

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

preparedStatement.setString(11, "html/css/js");

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

j = preparedStatement.executeUpdate();

if (j > 0) {

System.out.println("success");

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(preparedStatement, connection);

}

}

// 查看所有数据

@Test

public void search() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

String sql = "select * from course";

preparedStatement = connection.prepareStatement(sql);

resultSet = preparedStatement.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) {

e.printStackTrace();

} finally {

JDBCUtils.release(preparedStatement, connection, resultSet);

}

}

// 获取指定数据

@Test

public void searchByName() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

String sqlString = "select * from course where name=?";

preparedStatement = connection.prepareStatement(sqlString);

preparedStatement.setString(1, "java零基础");

resultSet = preparedStatement.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) {

e.printStackTrace();

} finally {

JDBCUtils.release(preparedStatement, connection, resultSet);

}

}

// 更新数据

@Test

public void updateByName() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

String sql = "update course set name = ? where name = ?";

preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1, "java语法");

preparedStatement.setString(2, "java零基础");

int i = preparedStatement.executeUpdate();

if (i > 0) {

System.out.println("success");

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(preparedStatement, connection, resultSet);

}

}

// 删除数据

@Test

public void deleteByName() {

Connection connection = null;

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

try {

connection = JDBCUtils.getConnection();

String sql = "delete from course where name = ?";

preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1, "java Web");

int i = preparedStatement.executeUpdate();

if (i > 0) {

System.out.println("success");

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(preparedStatement, 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 course order by createTime desc";

resultSet = statement.executeQuery(sqlString);

while (resultSet.next()) {

System.out.println(resultSet.getInt("id") + "--" + resultSet.getString("name") + "--"

+ resultSet.getString("category") + "--" + resultSet.getString("desp") + "--"

+ resultSet.getDate("createTime"));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

JDBCUtils.release(statement, connection, resultSet);

}

}

}