天天看點
傳回

【Java學習】JDBC1密碼正确否的判斷2 PreparedStatement3 特殊操作4 事務5 ORM6 DAO

1密碼正确否的判斷

給定使用者和密碼後，通過查詢資料庫中是否包含該條資料進行密碼正确與否的判斷

import java.sql.*;

public class Test2 {

    public static void main(String[] args){
        String dbURL = "jdbc:sqlserver://localhost:1434;DatabaseName=yfmMis";
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try(Connection c = DriverManager.getConnection(dbURL,"sa","123456")) {
            Statement s = c.createStatement();
            //建立表
            String query = "CREATE table TABLE2(ID NCHAR(2),NAME NCHAR(10),PASSWORD NCHAR(10))";
            s.executeUpdate(query);
            //插入資料
            String a1 = "INSERT INTO TABLE2 VALUES('1','小王','001')";
            String a2 = "INSERT INTO TABLE2 VALUES('2','小李','002')";
            String a3 = "INSERT INTO TABLE2 VALUES('3','小東','003')";
            s.executeUpdate(a1);
            s.executeUpdate(a2);
            s.executeUpdate(a3);
            //判斷賬号密碼是否正确
            String name = "小王";
            String password1 = "001";
            String password2 = "002";

            String sql = "select *from TABLE2 where name = '"+name+"'and password = '"+password1+"'";
            ResultSet rs = s.executeQuery(sql);

            if(rs.next()){
                System.out.println("right password");
            }
            else {
                System.out.println("wrong password");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } ;
    }
}

2 PreparedStatement

預編譯statement，可讀性好，性能更佳

import java.sql.*;

public class Test3 {
    public static void main(String[] args) {
        String dbURL = "jdbc:sqlserver://localhost:1434;DatabaseName=yfmMis";
        String sql = "insert into TABLE1 VALUES(?,?)";
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try {
            Connection c = DriverManager.getConnection(dbURL,"sa","123456");
            /*/使用preparedstatement
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1,"05");
            ps.setString(2,"李逍遙");
            ps.execute();
            */

            //擷取自增長ID??
            PreparedStatement ps2 = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps2.setString(1,"06");
            ps2.setString(2,"靈兒");
            ps2.execute();
            ResultSet rs = ps2.getGeneratedKeys();
            if(rs.next()){
                int id = rs.getInt(1);
                System.out.println(id);
            }
        }

        catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

3 特殊操作 

import java.sql.*;

public class Test4 {
    public static void main(String[] args){
        String dbURL = "jdbc:sqlserver://localhost:1434;DatabaseName=yfmMis";
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try(Connection c = DriverManager.getConnection(dbURL,"sa","123456")) {
            //擷取資料庫中繼資料

            DatabaseMetaData dbmd = c.getMetaData();
            System.out.println("擷取資料庫産品名稱"+dbmd.getDatabaseProductName());
            System.out.println("擷取資料庫産品版本"+dbmd.getDatabaseProductVersion());
            System.out.println("資料庫和表的分隔符"+dbmd.getCatalogSeparator());
            System.out.println("驅動版本"+dbmd.getDriverVersion());
            System.out.println("可用資料庫清單");
            ResultSet rs = dbmd.getCatalogs();
            while (rs.next()){
                System.out.println("資料庫名稱"+rs.getString(1));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } ;
    }
}

4 事務 

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


//事務
//通過 c.setAutoCommit(false);關閉自動送出
//使用 c.commit();進行手動送出
//處于同一個事務當中，要麼都成功，要麼都失敗


public class Test5 {
    public static void main(String[] args){
        String dbURL = "jdbc:sqlserver://localhost:1434;DatabaseName=yfmMis";
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try(Connection c = DriverManager.getConnection(dbURL,"sa","123456")) {
            Statement s = c.createStatement();

            c.setAutoCommit(false);
            String a1 = "update TABLE1 set ID = '1'WHERE NAME = '小王'";
            s.execute(a1);

            //a2語句有誤導緻a1也沒有被送出
            String a2 = "update TABLE1 set ID = '02'WHERE NAME = '小李'";
            s.execute(a2);
            Scanner sc = new Scanner(System.in);
            while(true) {

                System.out.println("是否執行更新操作,輸入y更新，輸入n不更新");
                String a = sc.next();
                if ("y".equals(a)) {
                    System.out.println("送出更新");
                    c.commit();
                    break;
                }
                else if("n".equals(a)) {
                    System.out.println("不送出更新");
                    break;
                }


            }



        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5 ORM 

import java.sql.*;


//ORM=Object Relationship Database Mapping
//
//對象和關系資料庫的映射
//
//簡單說，一個對象，對應資料庫裡的一條記錄

public class Test6 {

    public static Person get(String getid){
        Person p = null;
        String dbURL = "jdbc:sqlserver://localhost:1434;DatabaseName=yfmMis";
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try(Connection c = DriverManager.getConnection(dbURL,"sa","123456")) {

            Statement s = c.createStatement();
            String sql = "SELECT * FROM TABLE1 WHERE ID = '"+getid+"'";
            ResultSet rs = s.executeQuery(sql);

            while(rs.next()){
                 p = new Person();
                p.id = rs.getString(1);
                p.name = rs.getString(2);
            }




        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;

    }

    public static void main(String[] args){
        Person pe = get("1");
        System.out.println(pe.id+pe.name);

    }
}

6 DAO 

import java.sql.*;

//DAO=DataAccess Object
//
//資料通路對象
//
//實際上就是運用了練習-ORM中的思路，把資料庫相關的操作都封裝在這個類裡面，其他地方看不到JDBC的代碼
public class Test7 implements DAO {

    public static void main(String[] args){
        Test7 t7 = new Test7();

        Person p1 = new Person();
        p1.id = "09";
        p1.name = "大餅兄";

        Person p2 = new Person();
        p2.id = "09";
        p2.name = "月餅妹";
        t7.gettotal();
        t7.add(p1);

    }
    public Test7() {

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    public Connection gc() throws SQLException {
        String dbURL = "jdbc:sqlserver://localhost:1434;DatabaseName=yfmMis";
        return DriverManager.getConnection(dbURL, "sa", "123456");
    }

    public int gettotal() {
        int i = 0;

        try (Connection c = gc(); Statement s = c.createStatement()) {
            String sql = "select count(*) from TABLE1";
            ResultSet rs = s.executeQuery(sql);

            while (rs.next()) {
                i = rs.getInt(1);
            }

            System.out.println("total" + i);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public void add(Person p) {
        String sql = "insert into TABLE1 values(?,?)";
        try (Connection c = gc(); PreparedStatement s = c.prepareStatement(sql)) {
            s.setString(1, p.id);
            s.setString(2, p.name);
            s.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }



    public void update(Person p) {
        String sql = "update  TABLE1 set name = ?where id = ?";
        try (Connection c = gc(); PreparedStatement s = c.prepareStatement(sql)) {

            s.setString(1, p.id);
            s.setString(2, p.name);
            s.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    public void delete(String id) {
        String sql = "delete from  TABLE1 where id = ?";
        try (Connection c = gc(); PreparedStatement s = c.prepareStatement(sql)) {
            s.setString(1, id);
            s.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    public Person get(String id) {
        String sql = "select from  TABLE1 where id = ?";
        Person p = new Person();
        try (Connection c = gc(); PreparedStatement s = c.prepareStatement(sql)) {
            s.setString(1, id);
            s.execute();

            ResultSet rs = s.executeQuery();

            while (rs.next()){

                p.id = id;
                p.name = rs.getString(2);
                System.out.println("編号為"+id+"的人是"+rs.getString(2));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }




}
上一篇: Ubuntu下安裝mongo db1.擷取安裝包2.安裝用戶端3.通路資料庫4.設定查詢行數
下一篇: Git 之 checkout 指令解析