首页手记 JDBC之对面的女孩看过来代码

JDBC之对面的女孩看过来代码

标签：

Java MySQL

//数据库
/*
Navicat MySQL Data Transfer

Source Server : lan
Source Server Version : 50622
Source Host : localhost:3306
Source Database : imooc

Target Server Type : MYSQL
Target Server Version : 50622
File Encoding : 65001

Date: 2017-03-28 07:47:22
*/

SET FOREIGN_KEY_CHECKS=0;

-- Table structure for god

DROP TABLE IF EXISTS god;
CREATE TABLE god (
id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) NOT NULL,
sex varchar(255) DEFAULT NULL,
age int(11) DEFAULT NULL,
birthday date DEFAULT NULL,
email varchar(255) DEFAULT NULL,
mobile varchar(255) DEFAULT NULL,
create_user varchar(255) DEFAULT NULL,
create_date date DEFAULT NULL,
update_user varchar(255) DEFAULT NULL,
update_date date DEFAULT NULL,
isdel int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- Records of god

INSERT INTO god VALUES ('1', '张炳建', '0', '35', '2017-03-08', null, null, null, null, null, null, null);
INSERT INTO god VALUES ('2', '谢霆锋', '0', '28', '1970-01-01', null, null, null, '2017-03-27', null, null, null);

//获取数据库连接
package com.imooc.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {

//获取文件
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;

static{
    Properties pro=new Properties();
    try {
        pro.load(DBUtil.class.getResourceAsStream("/db.properties"));

        driver=pro.getProperty("driver");
        url=pro.getProperty("url");
        username=pro.getProperty("username");
        password=pro.getProperty("password");

        Class.forName(driver);

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out.println("获取db.properties出错");
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

//获取数据库连接
public static Connection getConn(){

    try {
        return DriverManager.getConnection(url, username, password);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out.println("数据库连接出错");
    }
    return null;
}

//关闭资源
public static void getClose(Object...objs){
    if(objs!=null&&objs.length>0){
        for(int i=0;i<objs.length;i++){
            try {
                if(objs[i] instanceof Connection){
                    ((Connection)objs[i]).close();
                }else if(objs[i] instanceof PreparedStatement){
                    ((PreparedStatement)objs[i]).close();
                }else if(objs[i] instanceof ResultSet){
                    ((ResultSet)objs[i]).close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                System.out.println("关闭资源出错");
            }
        }
    }
}

public static void main(String[] args) {
    DBUtil.getConn();

}

}

//po类
package com.imooc.po;

import java.util.Date;

public class God {

private Integer id;
private String user_name;
private Integer sex;
private Integer age;
private Date birthday;
private String email;
private String mobile;
private String create_user;
private Date create_date;
private String update_user;
private Date update_date;
private Integer isdel;
public Integer getId() {
    return id;
}
public void setId(Integer id) {
    this.id = id;
}
public String getUser_name() {
    return user_name;
}
public void setUser_name(String user_name) {
    this.user_name = user_name;
}
public Integer getSex() {
    return sex;
}
public void setSex(Integer sex) {
    this.sex = sex;
}
public Integer getAge() {
    return age;
}
public void setAge(Integer age) {
    this.age = age;
}
public Date getBirthday() {
    return birthday;
}
public void setBirthday(Date birthday) {
    this.birthday = birthday;
}
public String getEmail() {
    return email;
}
public void setEmail(String email) {
    this.email = email;
}
public String getMobile() {
    return mobile;
}
public void setMobile(String mobile) {
    this.mobile = mobile;
}
public String getCreate_user() {
    return create_user;
}
public void setCreate_user(String create_user) {
    this.create_user = create_user;
}
public Date getCreate_date() {
    return create_date;
}
public void setCreate_date(Date create_date) {
    this.create_date = create_date;
}
public String getUpdate_user() {
    return update_user;
}
public void setUpdate_user(String update_user) {
    this.update_user = update_user;
}
public Date getUpdate_date() {
    return update_date;
}
public void setUpdate_date(Date update_date) {
    this.update_date = update_date;
}
public Integer getIsdel() {
    return isdel;
}
public void setIsdel(Integer isdel) {
    this.isdel = isdel;
}
@Override
public String toString() {
    return "God [id=" + id + ", user_name=" + user_name + ", sex=" + sex + ", age=" + age + ", birthday=" + birthday
            + ", email=" + email + ", mobile=" + mobile + ", create_user=" + create_user + ", create_date="
            + create_date + ", update_user=" + update_user + ", update_date=" + update_date + ", isdel=" + isdel
            + "]";
}

}

//DAO接口
package com.imooc.dao;

import java.util.List;
import java.util.Map;

import com.imooc.po.God;

public interface GodDaoImpl {

public void addGod(God god);
public void updateGod(God god);
public void deleteGod(Integer id);
public List<God> queryGod();
public God queryById(Integer id);
public List<God> queryParams(List<Map<String,Object>> params);

}

//DAO层
package com.imooc.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.imooc.po.God;
import com.imooc.util.DBUtil;

public class GodDao implements GodDaoImpl {

Connection conn=DBUtil.getConn();
private PreparedStatement ps=null;
//添加
@Override
public void addGod(God g) {
    // TODO Auto-generated method stub

    String sql=" insert into "
            + " god(user_name,sex,age,birthday,email,mobile,create_user,create_date,update_user) "
            + " values(?,?,?,?,?,?,?,current_date,?)";
    try {
        ps=conn.prepareStatement(sql);

        ps.setString(1, g.getUser_name());
        ps.setInt(2, g.getSex());
        ps.setInt(3, g.getAge());
        ps.setDate(4, new Date(g.getBirthday().getTime()));
        ps.setString(5, g.getEmail());
        ps.setString(6, g.getMobile());
        ps.setString(7, g.getCreate_user());
        ps.setString(8, g.getUpdate_user());

    ps.execute();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

//更新
@Override
public void updateGod(God g) {
    // TODO Auto-generated method stub
    String sql=" update god set user_name=?,age=? where id=?";
    try {
        ps=conn.prepareStatement(sql);

        ps.setString(1, g.getUser_name());
        ps.setInt(2, g.getAge());
        ps.setInt(3, g.getId());

    ps.execute();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

//删除
@Override
public void deleteGod(Integer id) {
    // TODO Auto-generated method stub
    String sql=" delete from god where id=?";
    try {
        ps=conn.prepareStatement(sql);
        ps.setInt(1, id);

    ps.execute();

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

//查询所有的男神
@Override
public List<God> queryGod() {
    // TODO Auto-generated method stub
    List<God> list=new ArrayList<God>();
    God g=null;
    String sql="select * from god";
    try {
        ps=conn.prepareStatement(sql);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
            g=new God();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));

            list.add(g);
        }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return list;
}

//根据Id查询一位男神
@Override
public God queryById(Integer id) {
    // TODO Auto-generated method stub
    String sql="select * from god where id=?";
    God g=null;
    try {
        ps=conn.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs=ps.executeQuery();
        while(rs.next()){
            g=new God();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));
        }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return g;
}

//多参数查询
@Override
public List<God> queryParams(List<Map<String,Object>> params) {
    // TODO Auto-generated method stub
    List<God> list=new ArrayList<God>();
    God g=null;
    StringBuffer sb=new StringBuffer();
    sb.append("select * from god where 1=1 ");
    //sb.append("and user_name=? and id=? and age=?");

    if(params!=null&&params.size()>0){
        for (int i = 0; i < params.size(); i++) {
            Map<String, Object> map=params.get(i);
            sb.append(" and "+map.get("name")+" "+map.get("rela")+" "+map.get("value"));
        }
    }
    try {
        ps=conn.prepareStatement(sb.toString());
        System.out.println(sb.toString());
        ResultSet rs=ps.executeQuery();

        while(rs.next()){
            g=new God();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));

            list.add(g);
        }

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return list;
}

}

//测试
package com.imooc.test;

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.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.spi.DirStateFactory.Result;

import com.imooc.dao.GodDao;
import com.imooc.po.God;
import com.imooc.util.DBUtil;

public class godTest {

//测试添加
public static void addGod(){
    GodDao dao=new GodDao();
    God g=new God();

    g.setUser_name("林志颖");
    g.setSex(1);
    g.setAge(35);
    g.setBirthday(new Date());

    dao.addGod(g);

}
//测试查询所有男神
public static void queryGod(){
    GodDao dao=new GodDao();
    List<God> gs=dao.queryGod();
    for(God g:gs){
        System.out.println(g.toString());
    }

}
//测试更新
public static void update(){
    GodDao dao=new GodDao();
    God g=new God();
    g.setUser_name("张炳建");
    g.setAge(35);
    g.setId(1);

    dao.updateGod(g);;
}
//测试删除
public static void delete(){
    GodDao dao=new GodDao();

    dao.deleteGod(3);
}
//测试根据Id查询一位男神
public static void queryOne(){
    GodDao dao=new GodDao();
    God g=dao.queryById(2);

    System.out.println(g.toString());
}
//多参数查询
public static void queryMany(){
    GodDao dao=new GodDao();

    List<Map<String,Object>> params=new ArrayList<Map<String,Object>>();

    Map<String, Object> param=new HashMap<String, Object>();
    param.put("name", "user_name");
    param.put("rela", "like");
    param.put("value", "'%谢霆锋%'");
    params.add(param);

    param=new HashMap<String, Object>();
    param.put("name", "id");
    param.put("rela", "=");
    param.put("value", "2");
    params.add(param);

    List<God> list=dao.queryParams(params);

    for (int i = 0; i < list.size(); i++) {
        System.out.println(list.get(i).toString());
    }

}
public static void main(String[] args) {
    //addGod();
    //queryGod();
    //update();
    //delete();
    //queryOne();
    queryMany();

}

}

//action层
package com.imooc.action;

import java.util.List;
import java.util.Map;

import com.imooc.dao.GodDao;
import com.imooc.po.God;

public class GodAction {
// public void addGod(God god);
// public void updateGod(God god);
// public void deleteGod(Integer id);
// public List<God> queryGod();
// public God queryById(Integer id);
// public List<God> queryParams(List<Map<String,Object>> params);
GodDao dao=new GodDao();

public void add(God g){
    dao.addGod(g);
}

public void update(God g){
    dao.updateGod(g);
}

public void delete(Integer id){
    dao.deleteGod(id);
}

public List<God> query(){
    return dao.queryGod();
}

public God queryById(Integer id){
    return dao.queryById(id);
}

public List<God> queryParams(List<Map<String,Object>> params){
    return dao.queryParams(params);
}

}

点击查看更多内容