从JAVA到JDBC的感受

1.在JDBC中excuteUpdate()与executeUpdate(sql)的区别：

如果执行的sql语句中有变量，则应选用不带参数的excute语句，若sql语句不带变量，则应选用不带参数的语句。

2.带变量的sql语句可以有两种方式表示（以insert语句为例）：

1⃣️ PreparedStatment stmt = new PreparedStatment()

sql = "insert into tableName (a,b,c,d,e) values(?,?,?,?,?)";

stmt.set[String](1,a);

stmt.set[String](2,b);

stmt.set[String](3,c);

stmt.set[String](4,d);

stmt.set[String](5,e);

2⃣️ Statementst =conn.createStatement();        

        String sql = "insert into tableName (a,b) values ('"+a+"','"+b+"')";  

3.可以定义配置文件以工厂形式来存放数据库连接的数据；

4.可以将数据库中的列封装到对象<List>中;

5.初步代码如下：

MysqlCon.java

import java.io.FileReader;import java.io.Reader;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;import com.mysql.jdbc.PreparedStatement;public class MysqlCon {              private static String driver;        private static String url;       private static String username;       private static String password;              static{       Properties prop = new Properties();       Reader in;       try{       in = new FileReader("src//config.properties");       prop.load(in);       }catch(Exception e){       e.printStackTrace();       }            driver = prop.getProperty("driver");       url = prop.getProperty("url");       username = prop.getProperty("username");       password = prop.getProperty("password");              try{       Class.forName(driver);       }catch(ClassNotFoundException e){       System.out.println(e);       }       }              public static String Mysqllogin_compare(String username_cp){       try {       Connection conn = DriverManager.getConnection(url, username, password);       if(!conn.isClosed()){                         System.out.println("Succeeded connecting to the Database!");                            Statement st = conn.createStatement();                   String sql = "select * from user where user.name='"+username_cp+"'";                 ResultSet rs = st.executeQuery(sql);               String password_cp = null;               if(rs.next()){               password_cp = rs.getString(5);               }               rs.close();                      conn.close();               return password_cp;       }         }catch (SQLException e) {//      e.printStackTrace();       }       return null;       }              public void Mysql_save(String name, String num, String sex, int age){       String name_save = name;       String num_save = num;       String sex_save = sex;       int age_save = age;       String password_save = num;       try {       Connection conn = DriverManager.getConnection(url, username, password);       if(!conn.isClosed()){                         System.out.println("Succeeded connecting to the Database!");                              String sql = "insert into user (name,num,age,sex,password) values(?,?,?,?,?)";                 String sql1 = "insert into base (ANAME,NUM,ACCOUNT) values(?,?,?)";               PreparedStatement st= (PreparedStatement) conn.prepareStatement(sql);                   st.setString(1, name_save);                   st.setString(2, num_save);                   st.setInt(3, age_save);                   st.setString(4, sex_save);                   st.setString(5, password_save);               st.executeUpdate();               PreparedStatement st1= (PreparedStatement) conn.prepareStatement(sql1);               st1.setString(1, name_save);               st1.setString(2, num_save);               st1.setInt(3, 0);           st1.executeUpdate();                          System.out.println("注册成功");             } conn.close();       }catch(Exception e){       e.printStackTrace();       }       }              public static void save_money(String username_cp,int account_cp){       String name = username_cp;       int account = 0;       int account_inc = account_cp;       try {       Connection conn = DriverManager.getConnection(url, username, password);       if(!conn.isClosed()){                         System.out.println("Succeeded connecting to the Database!");                            Statement st = conn.createStatement();                String sql1 = "select * from base where base.ANAME='"+name+"'";               ResultSet rst = st.executeQuery(sql1);               if(rst.next()){               account = rst.getInt(3);//               System.out.println(account);               }               account = account + account_inc;           String sql = "update base set base.ACCOUNT='"+account+"' where base.ANAME='"+name+"'";                 st.executeUpdate(sql);               System.out.println("存款成功");               rst.close();               conn.close();         }         }catch (SQLException e) {//      e.printStackTrace();       }          }              public static void out_money(String username_cp,int account_cp){       String name = username_cp;       int account = 0;       int out_account = account_cp;       try {       Connection conn = DriverManager.getConnection(url, username, password);       if(!conn.isClosed()){                         System.out.println("Succeeded connecting to the Database!");                            Statement st = conn.createStatement();                String sql1 = "select * from base where base.ANAME='"+name+"'";               ResultSet rst = st.executeQuery(sql1);               if(rst.next()){               account = rst.getInt(3);//               System.out.println(account);               }               if((account - out_account) >= 0){               account = account - out_account;               String sql = "update base set base.ACCOUNT='"+account+"' where base.ANAME='"+name+"'";                 st.executeUpdate(sql);               System.out.printf("取款成功，您的账户余额为%d",account);               rst.close();               conn.close();                 }       }       else{       System.out.println("您的余额不足，别逗了!");       }         }catch (SQLException e) {//      e.printStackTrace();       }          }public static void pass_money(String username_cp, String num_cp, int account_cp) {String name = username_cp;String num = num_cp;     int out_account = account_cp;     int left_account = 0;     int inc_account = 0;     try {    Connection conn = DriverManager.getConnection(url, username, password);    if(!conn.isClosed()){                      System.out.println("Succeeded connecting to the Database!");                         Statement st = conn.createStatement();             String sql1 = "select * from base where base.ANAME='"+name+"'";            ResultSet rst = st.executeQuery(sql1);            if(rst.next()){            left_account = rst.getInt(3);//            System.out.println(account);            }            if((left_account - out_account) >= 0){            left_account = left_account - out_account;            String sql = "update base set base.ACCOUNT='"+left_account+"' where base.ANAME='"+name+"'";              st.executeUpdate(sql);            String sq3 = "select * from base where base.NUM='"+num+"'";            ResultSet rst2 = st.executeQuery(sq3);            if(rst2.next()){            inc_account = rst2.getInt(3);            }            inc_account = inc_account + out_account;            String sq2 = "update base set base.ACCOUNT='"+inc_account+"' where base.NUM='"+num+"'";            st.executeUpdate(sq2);            System.out.printf("转账成功，您的账户余额为%d\n",left_account);            rst2.close();            rst.close();            conn.close();              }            else{            System.out.println("您的余额不足，别逗了!");            }    }      }catch (SQLException e) {//   e.printStackTrace();    }}     public static void lord_money(String username_cp,int account_cp){     String name = username_cp;     int account = 0;     int account_inc = account_cp;     try {     Connection conn = DriverManager.getConnection(url, username, password);     if(!conn.isClosed()){                       System.out.println("Succeeded connecting to the Database!");                          Statement st = conn.createStatement();              String sql1 = "select * from base where base.ANAME='"+name+"'";             ResultSet rst = st.executeQuery(sql1);             if(rst.next()){             account = rst.getInt(3);//             System.out.println(account);             }             account = account + account_inc;         String sql = "update base set base.ACCOUNT='"+account+"' where base.ANAME='"+name+"'";               st.executeUpdate(sql);             System.out.println("贷款成功");//             System.out.println("您的还款日期为");             rst.close();             conn.close();       }       }catch (SQLException e) {//    e.printStackTrace();     }      }  public static void show_money(String username_cp){ String name = username_cp;      int account = 0;     try {     Connection conn = DriverManager.getConnection(url, username, password);     if(!conn.isClosed()){                       System.out.println("Succeeded connecting to the Database!");                          Statement st = conn.createStatement();              String sql1 = "select * from base where base.ANAME='"+name+"'";             ResultSet rst = st.executeQuery(sql1);             if(rst.next()){             account = rst.getInt(3);//             System.out.println(account);             }             System.out.println(account);             rst.close();             conn.close();       }       }catch (SQLException e) {//    e.printStackTrace();     }      }}

ShowWindow.java

import java.util.Scanner;public class ShowWindow {private static String name;private static int in_account;private static int out_account;private static int pass_account;private static String pass_num;private static int lord_account;static MysqlCon msq = new MysqlCon(); public static void show_menu( ){System.out.println(".......1.存款.......\n");System.out.println(".......2.取款.......\n");System.out.println(".......3.转账.......\n");System.out.println(".......4.贷款.......\n");System.out.println(".......5.退出.......\n");System.out.println(".......6.显示余额.......\n");}public static void main(String[] args){System.out.println("请登陆：新用户请输入1创建账户，老用户请输入2登陆");Scanner sc = new Scanner(System.in);int choice = sc.nextInt();if(choice == 2){System.out.println("请输入姓名");name = sc.next();System.out.println("请输入您的密码，您只有三次机会去输入正确的密码\n，如果超过三次，"+ "处于对您财产安全的考虑，您的账户将会被"+ "冻结一小时，请谅解");int count = 1;while(count <= 3){String password = sc.next();if(password.equals(MysqlCon.Mysqllogin_compare(name))){break;}else{System.out.println("\n请输入正确的密码，您还有两次机会");count++;}}if(count > 3)choice = 2;}else if(choice == 1){System.out.println("请输入您的姓名:");String name = sc.next();System.out.println("请输入您的学号:");String num = sc.next();System.out.println("请输入您的性别:");String sex = sc.next();System.out.println("请输入您的年龄:");int age = sc.nextInt();System.out.println("您的密码将自动生成为您的学号:\n");msq.Mysql_save(name, num, sex, age);System.out.println("创建成功，即将退回到登陆界面\n");}System.out.println("登陆成功\n");System.out.println("请选择您要进行的操作\n");int num = 1;while(num != 0){show_menu();num = sc.nextInt();switch(num){case 1: System.out.println("你选择的是存款\n");System.out.println("请输入您要存入的数额：");in_account = sc.nextInt();MysqlCon.save_money(name,in_account);break;case 2: System.out.println("你选择的是取款\n");System.out.println("请输入您要取出的数额:");out_account = sc.nextInt();MysqlCon.out_money(name,out_account);break;case 3:System.out.println("你选择的是转账\n");int i = 1;while(i == 1){System.out.println("请输入您要转入的账号:");pass_num = sc.next();System.out.println("请输入转账金额");pass_account = sc.nextInt();MysqlCon.pass_money(name,pass_num,pass_account);break;}break;case 4:System.out.println("你选择的是贷款\n");System.out.println("请输入您要贷款的金额");lord_account = sc.nextInt();MysqlCon.lord_money(name,lord_account);break;case 5:System.out.println("你选择的是退出\n");num = 0;break;case 6:System.out.println("您选择的是显示余额\n");MysqlCon.show_money(name);}}sc.close();}}

配置文件