Java JDBC数据库链接

Java JDBC数据库链接

引入mysql-connector-java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DbUtil {

    public static final String URL = "jdbc:mysql://localhost:3306/imooc";
    public static final String USER = "liulx";
    public static final String PASSWORD = "123456";

    public static void main(String[] args) throws Exception {
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM imooc_goddess");
        //如果有数据，rs.next()返回true
        while(rs.next()){
            System.out.println(rs.getString("user_name")+" 年龄："+rs.getInt("age"));
        }
    }
}

增删改查

public class DbUtil {

public static final String URL = "jdbc:mysql://localhost:3306/imooc";
public static final String USER = "liulx";
public static final String PASSWORD = "123456";
private static Connection conn = null;
static{
    try {
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获得数据库连接
        conn = DriverManager.getConnection(URL, USER, PASSWORD);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static Connection getConnection(){
    return conn;
}

}

//模型
package liulx.model;

import java.util.Date;

public class Goddess {

private Integer id;
private String user_name;
private Integer sex;
private Integer age;
private Date birthday; //注意用的是java.util.Date
private String email;
private String mobile;
private String create_user;
private String update_user;
private Date create_date;
private Date update_date;
private Integer isDel;
//getter setter方法。。。

}

//---------dao层--------------
package liulx.dao;

import liulx.db.DbUtil;
import liulx.model.Goddess;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class GoddessDao {

//增加
public void addGoddess(Goddess g) throws SQLException {
    //获取连接
    Connection conn = DbUtil.getConnection();
    //sql
    String sql = "INSERT INTO imooc_goddess(user_name, sex, age, birthday, email, mobile,"+
        "create_user, create_date, update_user, update_date, isdel)"
            +"values("+"?,?,?,?,?,?,?,CURRENT_DATE(),?,CURRENT_DATE(),?)";
    //预编译
    PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

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

    //执行
    ptmt.execute();
}

public void updateGoddess(){
    //获取连接
    Connection conn = DbUtil.getConnection();
    //sql, 每行加空格
    String sql = "UPDATE imooc_goddess" +
            " set user_name=?, sex=?, age=?, birthday=?, email=?, mobile=?,"+
            " update_user=?, update_date=CURRENT_DATE(), isdel=? "+
            " where id=?";
    //预编译
    PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

    //传参
    ptmt.setString(1, g.getUser_name());
    ptmt.setInt(2, g.getSex());
    ptmt.setInt(3, g.getAge());
    ptmt.setDate(4, new Date(g.getBirthday().getTime()));
    ptmt.setString(5, g.getEmail());
    ptmt.setString(6, g.getMobile());
    ptmt.setString(7, g.getUpdate_user());
    ptmt.setInt(8, g.getIsDel());
    ptmt.setInt(9, g.getId());

    //执行
    ptmt.execute();
}

public void delGoddess(){
    //获取连接
    Connection conn = DbUtil.getConnection();
    //sql, 每行加空格
    String sql = "delete from imooc_goddess where id=?";
    //预编译SQL，减少sql执行
    PreparedStatement ptmt = conn.prepareStatement(sql);

    //传参
    ptmt.setInt(1, id);

    //执行
    ptmt.execute();
}

public List<Goddess> query() throws SQLException {
    Connection conn = DbUtil.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM imooc_goddess");

    List<Goddess> gs = new ArrayList<Goddess>();
    Goddess g = null;
    while(rs.next()){
        g = new Goddess();
        g.setUser_name(rs.getString("user_name"));
        g.setAge(rs.getInt("age"));

        gs.add(g);
    }
    return gs;
}

public Goddess get(){
    Goddess g = null;
    //获取连接
    Connection conn = DbUtil.getConnection();
    //sql, 每行加空格
    String sql = "select * from  imooc_goddess where id=?";
    //预编译SQL，减少sql执行
    PreparedStatement ptmt = conn.prepareStatement(sql);
    //传参
    ptmt.setInt(1, id);
    //执行
    ResultSet rs = ptmt.executeQuery();
    while(rs.next()){
        g = new Goddess();
        g.setId(rs.getInt("id"));
        g.setUser_name(rs.getString("user_name"));
        g.setAge(rs.getInt("age"));
        g.setSex(rs.getInt("sex"));
        g.setBirthday(rs.getDate("birthday"));
        g.setEmail(rs.getString("email"));
        g.setMobile(rs.getString("mobile"));
        g.setCreate_date(rs.getDate("create_date"));
        g.setCreate_user(rs.getString("create_user"));
        g.setUpdate_date(rs.getDate("update_date"));
        g.setUpdate_user(rs.getString("update_user"));
        g.setIsDel(rs.getInt("isdel"));
    }
    return g;
}

}