package entity;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Product {
private int id;
private String name;
private double price;
private Date date;
public Product() {
super();
}
public Product(int id, String name, double price, Date date) {
super();
this.id = id;
this.name = name;
this.price = price;
this.date = date;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@Override
public String toString() {
DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String str=this.date==null?"":df.format(date);
return "Product [id=" + id + ", name=" + name + ", price=" + price
+ ", date=" + str + "]";
}
}
package dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import common.DbUtils;
import entity.Product;
public class ProductDao {
public void insert(Product product){
String name=product.getName();
double price=product.getPrice();
DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String str=df.format(new Date());
String sql="insert into product(name,price,date)values('"+name+"','"+price+"','"+str+"')";
DbUtils.execute(sql);
}
public Product getProduct(int id){
Connection conn=null;
Statement st=null;
ResultSet rs=null;
String sql="select id,name,price,date from product where id="+id;
Product product=new Product();
try {
conn=DbUtils.getConnection();
st=conn.createStatement();
rs=st.executeQuery(sql);
while(rs.next()){
int i=1;
int id2=rs.getInt(i++);
String name=rs.getString(i++);
double price=rs.getDouble(i++);
//日期时间不能用getDate(xxx);来获取.应使用getTimestamp(xxx)
//Date date=rs.getDate(i++);
java.sql.Timestamp ts=rs.getTimestamp(i++);
product.setId(id2);
product.setName(name);
product.setPrice(price);
//product.setDate(date);
//把java.sql.Timestamp转换成java.util.Date
if(ts!=null){
product.setDate(new Date(ts.getTime()));
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbUtils.close(st, conn, rs);
}
return product;
}
public List<Product> getProductList(){
Connection conn=null;
Statement st=null;
ResultSet rs=null;
String sql="select id,name,price from product";
List<Product> list=new ArrayList<Product>();
try {
conn=DbUtils.getConnection();
st=conn.createStatement();
rs=st.executeQuery(sql);
while(rs.next()){
int i=1;
int id2=rs.getInt(i++);
String name=rs.getString(i++);
double price=rs.getDouble(i++);
Product product=new Product();
product.setId(id2);
product.setName(name);
product.setPrice(price);
list.add(product);
}
}catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbUtils.close(st, conn, rs);
}
return list;
}
public List<Product> getProduct(String name){
Connection conn=null;
Statement st=null;
ResultSet rs=null;
String sql="select id,name,price from product where name like '%"+name+"%'";
List<Product> list=new ArrayList<Product>();
try {
conn=DbUtils.getConnection();
st=conn.createStatement();
rs=st.executeQuery(sql);
while(rs.next()){
int i=1;
int id2=rs.getInt(i++);
String name2=rs.getString(i++);
double price2=rs.getDouble(i++);
Product product=new Product();
product.setId(id2);
product.setName(name2);
product.setPrice(price2);
list.add(product);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbUtils.close(st, conn, rs);
}
return list;
}
public void updateProduct(Product product){
String name=product.getName();
double price=product.getPrice();
String sql="update product set name='"+name+"',price='"+price+"' where id="+product.getId();
DbUtils.execute(sql);
}
public void deleteProduct(Product product){
String sql="delete from product where id="+product.getId();
DbUtils.execute(sql);
}
}
package daoTest;
import java.util.List;
import dao.ProductDao;
import entity.Product;
public class ProductDaoTest {
public static void main(String[] args) {
//TestInsert();
TestGet();
//TestGetList();
//TestGetProduct();
//TestUpdateProduct();
//TestDeleteProduct();
}
public static void TestInsert(){
ProductDao pd=new ProductDao();
Product product=new Product();
product.setName("mp9");
product.setPrice(800);
pd.insert(product);
}
public static void TestGet(){
ProductDao pd=new ProductDao();
Product product=new Product();
product=pd.getProduct(11);
System.out.println(product);
}
public static void TestGetList(){
ProductDao pd=new ProductDao();
List<Product> list=pd.getProductList();
for(Product product:list){
System.out.println(product);
}
}
public static void TestGetProduct(){
ProductDao pd=new ProductDao();
List<Product> list=pd.getProductList();
list=pd.getProduct("mp");
for(Product product:list){
System.out.println(product);
}
}
public static void TestUpdateProduct(){
ProductDao pd=new ProductDao();
Product product=new Product();
product.setId(4);
product.setName("ipad");
product.setPrice(600);
pd.updateProduct(product);
}
public static void TestDeleteProduct(){
ProductDao pd=new ProductDao();
Product product=new Product();
product.setId(4);
pd.deleteProduct(product);
}
}
package common;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
* 数据库的工具类
* @author sally
*
*/
public class DbUtils {
public static Connection getConnection(){
Connection conn=null;
String url="jdbc:mysql://localhost:3306/test";
String user="root";
String password="root";
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(Statement st,Connection conn,ResultSet rs){
close(rs);
close(st);
close(conn);
}
public static void close(Statement st,Connection conn){
close(st);
close(conn);
}
public static void close(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(Statement st){
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void execute(String sql){
Connection conn=null;
Statement st=null;
try {
conn=DbUtils.getConnection();
st=conn.createStatement();
st.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DbUtils.close(st, conn);
}
}
public static <T> T queryForObject(String sql,T t){
return null;
}
public static <T> List<T> queryForList(String sql,T t){
return null;
}
}