04 JDBC 之prepareStatement 执行CRUD

JDBC 之prepareStatement 执行CRUD

增加

package com.myweb.lesson03;

import com.myweb.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    public static void main(String[] args) {
        //使用prepareStatementCRUD
        Connection conn = null;
        PreparedStatement st = null;
        try {
            //连接数据库
            conn = JdbcUtils.getConnection();
            //编写sql
            String sql ="INSERT INTO users (`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (4,'张三','123456','zhangsan@email.com','2020-01-03')";
            //获取预处理对象
            st = conn.prepareStatement(sql);
            //执行sql
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("新增成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭连接，释放资源
            JdbcUtils.release(conn,st,null);
        }
    }
}

删

package com.myweb.lesson03;

import com.myweb.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    public static void main(String[] args) {
        //使用prepareStatementCRUD
        Connection conn = null;
        PreparedStatement st = null;
        try {
            //连接数据库
            conn = JdbcUtils.getConnection();
            //编写sql
            String sql ="INSERT INTO users (`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (4,'张三','123456','zhangsan@email.com','2020-01-03')";
            //获取预处理对象
            st = conn.prepareStatement(sql);
            //执行sql
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("新增成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭连接，释放资源
            JdbcUtils.release(conn,st,null);
        }
    }
}

改

package com.myweb.lesson03;

import com.myweb.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "UPDATE users set `NAME` ='蜡笔小新' where id =3";
            preparedStatement = connection.prepareStatement(sql);
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

查询

package com.myweb.lesson03;

import com.myweb.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "select * from users";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
                System.out.println(resultSet.getString("birthday"));
                System.out.println("==华丽的分割线==");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

Demo一个登陆方法

package com.myweb.lesson03;

import com.myweb.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestLogin {
    //模拟登陆方法
    public static void main(String[] args) {
        //获取用户输入
        String username = "张三";
        String password = "123456";

        //查询数据库
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();
            String sql ="SELECT * FROM users where `NAME`=? AND `PASSWORD`=?";

            preparedStatement = connection.prepareStatement(sql);
            //prepareSt的传参方式，Index 是?的位置，从1开始
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next())
            {
                System.out.println("登陆成功");
            }else {
                System.out.println("用户名或密码错误");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }

    }
}