JDBC初尝试

 

 

 

 

 

Test.java

 

 

package xu;

import java.sql.*;
public class test {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/day2jdbc?useUnicode=true&characterEncoding=UTF8&useSSL=false";//防止乱码
        String user="root";
        String pass="hmc3344djl";
        Connection conn=DriverManager.getConnection(url, user, pass);
        System.out.println(conn+",成功连接数据库");
        conn.close();
    }
}

 

 

 

Dbutils

package util;

import java.sql.*;

public class Dbutils {

    public Connection conn = null;

    public PreparedStatement pstmt = null;

    public ResultSet rs = null;

    public Connection getConnection() {

        try {

            //1、加载驱动类

            Class.forName("com.mysql.jdbc.Driver");

            //2、创建连接对象

            String url = "jdbc:mysql://localhost:3306/day2jdbc?useUnicode=true&characterEncoding=UTF8&useSSL=false";

            String user = "root";

            String password = "hmc3344djl";

            conn = DriverManager.getConnection(url,user,password);

        } catch (Exception e) {

            e.printStackTrace();

        }

        return conn;

    }



    /**

     * 关闭资源

     *

     * @param conn

     * @param pstmt

     * @param rs

     */

    public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {

        try {

            if (rs != null) {

                rs.close();

            }

            if (pstmt != null) {

                pstmt.close();

            }

            if (conn != null) {

                conn.close();

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }



    /**

     * 增删改操作

     *

     * @param sql

     * @param params

     * @return

     */

    public int executeUpdate(String sql, Object[] params) {

        this.getConnection();

        int result = 0;

        try {

            //3、创建prepareStatement对象

            pstmt = conn.prepareStatement(sql);

            //4、为占位符赋值

            if (null != params) {

                for (int i = 0; i < params.length; i++) {

                    pstmt.setObject(i + 1, params[i]);

                }

            }

            //5、调用方法：执行sql语句

            result = pstmt.executeUpdate();

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            this.closeAll(conn, pstmt, null);

        }

        return result;

    }



    /**

     * 查询方法

     *

     * @param sql

     * @param params

     * @return

     */

    public ResultSet executQuery(String sql, Object[] params) {

        this.getConnection();

        try {

            //3、创建prepareStatement对象

            pstmt = conn.prepareStatement(sql);

            //4、为占位符赋值

            if (null != params) {

                for (int i = 0; i < params.length; i++) {

                    pstmt.setObject(i + 1, params[i]);

                }

            }

            //5、调用方法：执行sql语句

            rs = pstmt.executeQuery();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        //后面具体的查询方法还需要用到rs ,所以此处最后不能关闭数据流

        return rs;

    }

}

 

 

User

package pojo;







public class User {

    private String id;

    private String name;

    private int age;

    private int level;



    public User(String id, String name, int age, int level) {

        this.id = id;

        this.name = name;

        this.age = age;

        this.level = level;

    }



    public User() {

    }



    public String getId() {

        return id;

    }



    public void setId(String id) {

        this.id = id;

    }



    public String getName() {

        return name;

    }



    public void setName(String name) {

        this.name = name;

    }



    public int getAge() {

        return age;

    }



    public void setAge(int age) {

        this.age = age;

    }



    public int getLevel() {

        return level;

    }



    public void setLevel(int level) {

        this.level = level;

    }

}

 

 

Userdao

package dao;





import pojo.User;

import util.Dbutils;



import java.sql.ResultSet;

import java.sql.SQLException;





public class Userdao {

    Dbutils dbutils = new Dbutils();

    public User findById(String id){

        User user = new User();

        String sql = "select * from user where id=?";

        Object[] params = {id};

        ResultSet rs = dbutils.executQuery(sql,params);

        try{

            if(null != rs){

                if(rs.next()){

                    user.setId(rs.getString("id"));

                    user.setName(rs.getString("name"));

                    user.setAge(rs.getInt("age"));

                    user.setLevel(rs.getInt("level"));



                }

            }

        }catch (SQLException ex) {

            ex.printStackTrace();

        }finally {

            dbutils.closeAll(dbutils.conn , dbutils.pstmt , dbutils.rs);

        }

        return user;

    }





    public  boolean insert(User user){

        boolean flag = false;

        String sql = "insert into user(id,name,age,level) values(?,?,?,?)";

        Object[] params ={ user.getId(),user.getName(),user.getAge(),user.getLevel()};

        return (dbutils.executeUpdate(sql,params)==1);

    }





    public  boolean update(User user){

        String sql = "update user set name=?,age=?,level WHERE id=?";

        Object[] params = {user.getId(),user.getName(),user.getAge(),user.getLevel()};

        return dbutils.executeUpdate(sql, params) == 1;

    }





    public boolean deleteById(String id){

        String sql = "delete from user where id=?";

        Object[] params = {id};

        return dbutils.executeUpdate(sql,params) == 1;

    }



}

 

 

demo

import org.junit.Test;

import pojo.User;

import dao.Userdao;



import java.util.List;



public class demo {







    @Test

    public void test1() {

        User user = new User("6", "cong", 24, 5);

        Userdao userDao = new Userdao();

        userDao.insert(user);

    }



    @Test

    public void test2() {

        String id = "5";

        Userdao userDao = new Userdao();

        userDao.deleteById(id);

    }



    @Test

    public void test3() {

        String id = "5";

        Userdao userDao = new Userdao();

        System.out.println(userDao.findById(id));

    }



    @Test

    public void test4(){

        User user = new User("5","zhao",18,0);

        Userdao userDao=new Userdao();

        userDao.update(user);



    }

}

 

maven

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>groupId</groupId>
    <artifactId>jdbctest</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
    
</project>

 

Test.java

 

 

package xu;

import java.sql.*;
public class test {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/day2jdbc?useUnicode=true&characterEncoding=UTF8&useSSL=false";//防止乱码
        String user="root";
        String pass="hmc3344djl";
        Connection conn=DriverManager.getConnection(url, user, pass);
        System.out.println(conn+",成功连接数据库");
        conn.close();
    }
}

 

 

 

Dbutils

package util;

import java.sql.*;

public class Dbutils {

    public Connection conn = null;

    public PreparedStatement pstmt = null;

    public ResultSet rs = null;

    public Connection getConnection() {

        try {

            //1、加载驱动类

            Class.forName("com.mysql.jdbc.Driver");

            //2、创建连接对象

            String url = "jdbc:mysql://localhost:3306/day2jdbc?useUnicode=true&characterEncoding=UTF8&useSSL=false";

            String user = "root";

            String password = "hmc3344djl";

            conn = DriverManager.getConnection(url,user,password);

        } catch (Exception e) {

            e.printStackTrace();

        }

        return conn;

    }



    /**

     * 关闭资源

     *

     * @param conn

     * @param pstmt

     * @param rs

     */

    public void closeAll(Connection conn, PreparedStatement pstmt, ResultSet rs) {

        try {

            if (rs != null) {

                rs.close();

            }

            if (pstmt != null) {

                pstmt.close();

            }

            if (conn != null) {

                conn.close();

            }

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }



    /**

     * 增删改操作

     *

     * @param sql

     * @param params

     * @return

     */

    public int executeUpdate(String sql, Object[] params) {

        this.getConnection();

        int result = 0;

        try {

            //3、创建prepareStatement对象

            pstmt = conn.prepareStatement(sql);

            //4、为占位符赋值

            if (null != params) {

                for (int i = 0; i < params.length; i++) {

                    pstmt.setObject(i + 1, params[i]);

                }

            }

            //5、调用方法：执行sql语句

            result = pstmt.executeUpdate();

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            this.closeAll(conn, pstmt, null);

        }

        return result;

    }



    /**

     * 查询方法

     *

     * @param sql

     * @param params

     * @return

     */

    public ResultSet executQuery(String sql, Object[] params) {

        this.getConnection();

        try {

            //3、创建prepareStatement对象

            pstmt = conn.prepareStatement(sql);

            //4、为占位符赋值

            if (null != params) {

                for (int i = 0; i < params.length; i++) {

                    pstmt.setObject(i + 1, params[i]);

                }

            }

            //5、调用方法：执行sql语句

            rs = pstmt.executeQuery();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        //后面具体的查询方法还需要用到rs ,所以此处最后不能关闭数据流

        return rs;

    }

}

 

 

User

package pojo;







public class User {

    private String id;

    private String name;

    private int age;

    private int level;



    public User(String id, String name, int age, int level) {

        this.id = id;

        this.name = name;

        this.age = age;

        this.level = level;

    }



    public User() {

    }



    public String getId() {

        return id;

    }



    public void setId(String id) {

        this.id = id;

    }



    public String getName() {

        return name;

    }



    public void setName(String name) {

        this.name = name;

    }



    public int getAge() {

        return age;

    }



    public void setAge(int age) {

        this.age = age;

    }



    public int getLevel() {

        return level;

    }



    public void setLevel(int level) {

        this.level = level;

    }

}

 

 

Userdao

package dao;





import pojo.User;

import util.Dbutils;



import java.sql.ResultSet;

import java.sql.SQLException;





public class Userdao {

    Dbutils dbutils = new Dbutils();

    public User findById(String id){

        User user = new User();

        String sql = "select * from user where id=?";

        Object[] params = {id};

        ResultSet rs = dbutils.executQuery(sql,params);

        try{

            if(null != rs){

                if(rs.next()){

                    user.setId(rs.getString("id"));

                    user.setName(rs.getString("name"));

                    user.setAge(rs.getInt("age"));

                    user.setLevel(rs.getInt("level"));



                }

            }

        }catch (SQLException ex) {

            ex.printStackTrace();

        }finally {

            dbutils.closeAll(dbutils.conn , dbutils.pstmt , dbutils.rs);

        }

        return user;

    }





    public  boolean insert(User user){

        boolean flag = false;

        String sql = "insert into user(id,name,age,level) values(?,?,?,?)";

        Object[] params ={ user.getId(),user.getName(),user.getAge(),user.getLevel()};

        return (dbutils.executeUpdate(sql,params)==1);

    }





    public  boolean update(User user){

        String sql = "update user set name=?,age=?,level WHERE id=?";

        Object[] params = {user.getId(),user.getName(),user.getAge(),user.getLevel()};

        return dbutils.executeUpdate(sql, params) == 1;

    }





    public boolean deleteById(String id){

        String sql = "delete from user where id=?";

        Object[] params = {id};

        return dbutils.executeUpdate(sql,params) == 1;

    }



}

 

 

demo

import org.junit.Test;

import pojo.User;

import dao.Userdao;



import java.util.List;



public class demo {







    @Test

    public void test1() {

        User user = new User("6", "cong", 24, 5);

        Userdao userDao = new Userdao();

        userDao.insert(user);

    }



    @Test

    public void test2() {

        String id = "5";

        Userdao userDao = new Userdao();

        userDao.deleteById(id);

    }



    @Test

    public void test3() {

        String id = "5";

        Userdao userDao = new Userdao();

        System.out.println(userDao.findById(id));

    }



    @Test

    public void test4(){

        User user = new User("5","zhao",18,0);

        Userdao userDao=new Userdao();

        userDao.update(user);



    }

}

 

 

增

 

删

 

查

 

改