java如何添改删查_2018-01-23 java 增加删除修改 查询

package com.company;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

/**

* Created by ttc on 18-1-22.

*/

public class JDBCDemoFinal {

//增删改

//修改

public static int updateSort(Sort sort) throws SQLException {

Connection connection = JDBCUtils.getConnection();

PreparedStatement preparedStatement =

connection.prepareStatement("update sort set sname = ?, sprice = ?, sdesc = ?, sdate = ? where sid = ?");

preparedStatement.setString(1,sort.getSname());

preparedStatement.setDouble(2,sort.getSprice());

preparedStatement.setString(3,sort.getSdesc());

//java.util.date--->java.sql.Timestamp

Date date = sort.getSdate();

//得到date对应的时间戳

long timestamp = date.getTime();

//通过时间戳创建java.sql.Timestamp

java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);

preparedStatement.setTimestamp(4,sqlDate);

preparedStatement.setInt(5,sort.getSid());

int rows = preparedStatement.executeUpdate();

return rows;

}

//删除

public static int deleteSortByID(int id) throws SQLException {

Connection connection = JDBCUtils.getConnection();

PreparedStatement preparedStatement = connection.prepareStatement("delete from sort where sid = ?");

preparedStatement.setInt(1,id);

int rows = preparedStatement.executeUpdate();

JDBCUtils.close(preparedStatement,connection);

return rows;

}

//新增

public static int AddSort(Sort sort) throws SQLException {

Connection connection = JDBCUtils.getConnection();

PreparedStatement preparedStatement = connection.prepareStatement("insert into sort(sname,sprice,sdesc,sdate) values(?,?,?,?)");

preparedStatement.setString(1,sort.getSname());

preparedStatement.setDouble(2,sort.getSprice());

preparedStatement.setString(3,sort.getSdesc());

//java.util.date--->java.sql.Timestamp

Date date = sort.getSdate();

//得到date对应的时间戳

long timestamp = date.getTime();

//通过时间戳创建java.sql.Timestamp

java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);

preparedStatement.setTimestamp(4,sqlDate);

int rows = preparedStatement.executeUpdate();

JDBCUtils.close(preparedStatement,connection);

return rows;

}

//查全部，查一个

public static Sort getSortByID(int id) throws SQLException {

Connection connection = JDBCUtils.getConnection();

PreparedStatement preparedStatement = connection.prepareStatement("select * from sort where sid = ?");

preparedStatement.setInt(1,id);

ResultSet resultSet = preparedStatement.executeQuery();

Sort sort = null;

if(resultSet.next() == true)

{

sort = new Sort();

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

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

double dPrice = resultSet.getDouble("sprice");

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

Date date = resultSet.getTimestamp("sdate");

sort.setSid(sid);

sort.setSprice(dPrice);

sort.setSname(sname);

sort.setSdesc(sdesc);

sort.setSdate(date);

}

JDBCUtils.close(resultSet,preparedStatement,connection);

return sort;

}

public static List getAllSorts() throws SQLException {

Connection connection = JDBCUtils.getConnection();

PreparedStatement preparedStatement = connection.prepareStatement("select * from sort");

List sortList = new ArrayList<>();

ResultSet resultSet = preparedStatement.executeQuery();

while(resultSet.next() == true)

{

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

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

double dPrice = resultSet.getDouble("sprice");

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

Date date = resultSet.getTimestamp("sdate");

Sort sort = new Sort();

sort.setSid(sid);

sort.setSprice(dPrice);

sort.setSname(sname);

sort.setSdesc(sdesc);

sort.setSdate(date);

sortList.add(sort);

}

JDBCUtils.close(resultSet,preparedStatement,connection);

return sortList;

}

public static void main(String[] args) throws SQLException {

System.out.println("查一个");

Sort sort = getSortByID(11);

if(sort != null)

{

System.out.println(sort);

}

else

{

System.out.println("没查到");

}

System.out.println("查全部");

List sortList = getAllSorts();

for(Sort sort1:sortList)

{

System.out.println(sort1);

}

System.out.println("删除");

//删除

deleteSortByID(8);

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

Sort sort1 = new Sort();

sort1.setSname("小饰品");

sort1.setSprice(5600);

sort1.setSdesc("好看");

Date date = new Date();

sort1.setSdate(date);

AddSort(sort1);

System.out.println("修改");

Sort sort2 = new Sort();

sort2.setSname("汽车");

sort2.setSprice(5600);

sort2.setSdesc("好看");

Date date2 = new Date();

sort2.setSdate(date2);

sort2.setSid(13);

}

}