Java学习42天--JDBC常见错误，PreparedStatement,综合案例

最新推荐文章于 2022-06-18 08:34:26 发布

lucasshj

最新推荐文章于 2022-06-18 08:34:26 发布

阅读量466

点赞数 1

分类专栏： JDBC 课堂案例文章标签： jdbc sql

本文链接：https://blog.csdn.net/lucasshj/article/details/105212052

版权

课堂案例同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

JDBC

2 篇文章 0 订阅

订阅专栏

千锋逆战班：孙华建
在千锋学习第42天
“未来的你会感谢今天奋斗的自己”
今天我学习了java课程，JDBC常见错误，PreparedStatement,综合案例
#中国加油！武汉加油！千锋加油！也为自己加油！！！#…

TestDql

package t2.dql;

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

public class TestDql {
    public static void main(String[] args)throws Exception {
        //1，注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2,获得连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/componydb?useUnicode=true&characterEncoding=utf8","root","19951023sun");

        //3,获取SQL的对象
        Statement statement = connection.createStatement();

        //4,编写SQL语句
        String sql = "select student_id,student_name,sex,birthday,phone,GradeId from stu;";
        ResultSet resultSet = statement.executeQuery(sql);

        //5,处理结果（结果集）
        while(resultSet.next()){
            String student_id = resultSet.getString("student_id");
            String student_name = resultSet.getString("student_name");
            String sex = resultSet.getString("sex");
            String birthday = resultSet.getString("birthday");
            String phone = resultSet.getString("phone");
            int gradeId = resultSet.getInt("GradeId");
            System.out.println(student_id+"\t"+student_name+"\t"+sex+"\t"+birthday+"\t"+phone+"\t"+gradeId);
        }

        //6,释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }
}

TestDqlForIndex

package t2.dql;

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

public class TestDqlForIndex {
    public static void main(String[] args)throws Exception {
        //1，注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2,获得连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/componydb?useUnicode=true&characterEncoding=utf8","root","19951023sun");

        //3,获取SQL的对象
        Statement statement = connection.createStatement();

        //4,编写SQL语句
        String sql = "select student_id,student_name,sex,birthday,phone,GradeId from stu;";
        ResultSet resultSet = statement.executeQuery(sql);

        //5,处理结果（结果集）
        while(resultSet.next()){
            String student_id = resultSet.getString(1);
            String student_name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            String birthday = resultSet.getString(4);
            String phone = resultSet.getString(5);
            int gradeId = resultSet.getInt(6);
            System.out.println(student_id+"\t"+student_name+"\t"+sex+"\t"+birthday+"\t"+phone+"\t"+gradeId);
        }

        //6,释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }
}

TestLogin

package t3.login;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class TestLogin {
    public static void main(String[] args)throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = scanner.nextLine();
        System.out.println("请输入密码");
        String password = scanner.nextLine();

        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2,获得连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/componydb?useUnicode=true&characterEncoding=utf8","root","19951023sun");
        //3,创建执行SQL语句的对象
        Statement statement = connection.createStatement();
        //4,编写SQL语句，执行SQL语句
        String sql = "select * from user where username = '"+username+"'and password = '"+password+"'";
        ResultSet resultSet = statement.executeQuery(sql);
        //5,处理结果
        if(resultSet.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
        //6,释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }
}

TestSafeLogin

package t3.login;

import java.sql.*;
import java.util.Scanner;

public class TestSafeLogin {
    public static void main(String[] args) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = scanner.nextLine();
        System.out.println("请输入密码");
        String password = scanner.nextLine();

        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2,获得连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/componydb?useUnicode=true&characterEncoding=utf8", "root", "19951023sun");
        //3,创建执行SQL语句的对象
        String sql = "select * from user where username =? and password = ?";
        //预编译SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //为占位符下标赋值
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        //4,执行SQL语句
        ResultSet resultSet = preparedStatement.executeQuery();
        //5,处理结果
        if (resultSet.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("登录失败");
        }
        //6,释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();

    }
}

综合案例：

1：创建Account数据库，表t_account,插入数据

CREATE TABLE `user`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(10) NOT NULL,
	`password` VARCHAR(10) NOT NULL,
	phone VARCHAR(11)
)CHARSET=utf8;

INSERT INTO `user`(username,PASSWORD,phone)VALUES('小明','12345','1111111111');
INSERT INTO `user`(username,PASSWORD,phone)VALUES('小红','22345','1111111112');

SELECT * FROM `user`;

CREATE DATABASE Account CHARACTER SET utf8;
USE Account;
CREATE TABLE t_account(
	cardId VARCHAR(20) PRIMARY KEY,
	PASSWORD VARCHAR(20) NOT NULL,
	username VARCHAR(10) NOT NULL,
	balance DOUBLE NOT NULL,
	phone VARCHAR(11)
)CHARSET = utf8;

SELECT * FROM t_account;

2：创建AccountSystem

package t4.account;

import java.sql.*;
import java.util.Scanner;

public class AccountSystem {
    Scanner scanner = new Scanner(System.in);
    private static Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    static {
        try {
            //1,加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2,获得连接对象
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Account?useUnicode=true&characterEncoding=utf8","root","19951023sun");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //开户
    public void regiter(){
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入用户名：");
        String username = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();
        System.out.println("请输入存款金额：");
        double balance = scanner.nextDouble();
        System.out.println("请输入预留手机号码：");
        String phone = scanner.next();

        try {
            //1,2步放入静态代码块，创建类时加载
            //3,创建执行SQL的对象
            String sql = "insert into t_account(cardId,password,username,balance,phone)value(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            //4,给占位符赋值
            preparedStatement.setString(1,cardId);
            preparedStatement.setString(2,password);
            preparedStatement.setString(3,username);
            preparedStatement.setDouble(4,balance);
            preparedStatement.setString(5,phone);
            //5,执行SQL语句
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("开户成功");
            }else {
                System.out.println("开户失败");
            }
        } catch (SQLException e){
            e.printStackTrace();
        } finally {
            try {
                if(preparedStatement!=null){
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //存款
    public void saveMoney(){
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();
        System.out.println("请输入存款金额");
        double money = scanner.nextDouble();
        if(money > 0){
            //存款操作
            String sql = "update t_account set balance = balance + ? where cardId = ? and password = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setDouble(1,money);
                preparedStatement.setString(2,cardId);
                preparedStatement.setString(3,password);
                //执行接收返回结果
                int i = preparedStatement.executeUpdate();
                if(i>0){
                    System.out.println("存款成功");
                }else{
                    System.out.println("存款失败，请核对用户名或密码！");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    if(preparedStatement!=null){
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }else{
            System.out.println("您输入的金额不正确！");
        }
    }

    //取款
    public void takemoney(){
        System.out.println("请输入卡号：");
        String cardId = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();
        System.out.println("请输入取款金额");
        double money = scanner.nextDouble();
        if(money > 0){
            //1,先查询cardId和password对应的数据金额
            String sql = "select balance from t_account where cardId = ? and password = ?";
            try {
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,cardId);
                preparedStatement.setString(2,password);
                resultSet = preparedStatement.executeQuery();
                if(resultSet.next()){
                    double balance = resultSet.getDouble(1);
                    if(money<=balance){
                        //取款操作
                        String  sql2 = "update t_account set balance = balance - ? where cardId = ? and password = ?";
                        preparedStatement = connection.prepareStatement(sql2);
                        preparedStatement.setDouble(1,money);
                        preparedStatement.setString(2,cardId);
                        preparedStatement.setString(3,password);
                        int i = preparedStatement.executeUpdate();
                        if(i>0){
                            System.out.println("取款成功，欢迎下次光临");
                        }else{
                            System.out.println("取款失败，请核对用户名或密码");
                        }
                    }else{
                        System.out.println("您的余额不足！");
                    }
                    String sql3 = "select balance from t_account where cardId = ? and password = ?";
                    preparedStatement = connection.prepareStatement(sql3);
                    preparedStatement.setString(1,cardId);
                    preparedStatement.setString(2,password);
                    resultSet = preparedStatement.executeQuery();
                    if(resultSet.next()){
                        System.out.println("您的余额为："+resultSet.getDouble(1));
                    }

                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    if(resultSet!=null){
                        resultSet.close();
                    }
                    if(preparedStatement!=null){
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }else{
            System.out.println("您输入的金额不正确！");
        }
    }

    public void closeConnection(){
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

3：TestAccount

package t4.account;

import java.util.Scanner;

public class TestAccount {
    public static void main(String[] args) {
        AccountSystem as = new AccountSystem();
        Scanner scanner = new Scanner(System.in);
        System.out.println("欢迎来到银行账户系统");
        int choice = 0;
        do{
            System.out.println("1、开户  2、存款  3、取款  4、转账  5、修改密码  6、注销  0、退出");
            System.out.println("请选择");
            choice = scanner.nextInt();
            switch (choice){
                case 1:
                    as.regiter();
                    break;
                case 2:
                    as.saveMoney();
                    break;
                case 3:
                    as.takemoney();
                    break;
                case 4:
                    break;
                case 5:
                    break;
                case 6:
                    break;
                case 0:
                    as.closeConnection();
                    return;
            }

        }while (choice!=0);
    }
}

lucasshj

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Java学习42天--JDBC常见错误，PreparedStatement,综合案例

千锋逆战班：孙华建在千锋学习第42天“未来的你会感谢今天奋斗的自己”今天我学习了java课程，JDBC常见错误，PreparedStatement,综合案例#中国加油！武汉加油！千锋加油！也为自己加油！！！#…TestDqlpackage t2.dql;import java.sql.Connection;import java.sql.DriverManager;import...
复制链接

扫一扫