【Java学习】JDBC

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;
    }




}