jdbc

简单增删改查

package Practice170829;

import org.junit.Test;

import java.sql.*;

/**
 * Created by 8102 on 2017/8/29.
 */
public class JDBC01 {
    @Test
    public void queryAll(){ //查询所有
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            // 1 加载JDBC驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 2 连接数据库
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/my_test?useUnicode=true&character=utf-8",
                    "root","root");
            // 3 准备执行的sql对象
            String sql = "SELECT * FROM users";
            pstmt = conn.prepareStatement(sql);
            // 4 执行sql语句,并显示结果
            rs = pstmt.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("user_name")+":"+rs.getInt("user_age"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5 关闭 先开的后关，后开的先关
            try {
                if(rs != null){
                    rs.close();
                }
                if(pstmt != null){
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void queryByConditions(){    //条件查询
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/my_test?useUnicode=true&character=utf-8",
                    "root","root");
            String sql = "SELECT * FROM users WHERE user_age>25;";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("user_name")+":"+rs.getInt("user_age"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(rs != null){
                    rs.close();
                }
                if(pstmt != null){
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void del(){  //删除
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/my_test?useUnicode=true&character=utf-8",
                    "root","root");
            String sql = "DELETE FROM users WHERE user_name=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"wangqing");
            int flag = pstmt.executeUpdate();
            if(flag>0){
                System.out.println("删除成功！");
            } else {
                System.out.println("删除失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt != null){
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void update(){  //修改
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/my_test?useUnicode=true&character=utf-8",
                    "root","root");
            String sql = "UPDATE users SET user_age=? WHERE user_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,0);
            pstmt.setInt(2,2);
            int flag = pstmt.executeUpdate();
            if(flag>0){
                System.out.println("修改成功！");
            } else {
                System.out.println("修改失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt != null){
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void add(){  //增加
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/my_test?useUnicode=true&character=utf-8",
                    "root","root");
            String sql = "INSERT INTO login(username,pwd) VALUES(?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"");
            pstmt.setString(2,"514");
            int flag = pstmt.executeUpdate();
            if(flag>0){
                System.out.println("增加成功！");
            } else {
                System.out.println("增加失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt != null){
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

存储过程和函数