JDBC

JBDC六步骤：

1.注册驱动

(1)Class.forName(driver)

(2)DriverManager.registerDriver(new com.mysql.jdbc.Driver())

(3)资源绑定器 

　　 ResourceBundle bundle=ResourceBundle.getBundle("文件名没有后缀")   以properties结尾的

　　bundle.getString(key)

2.获取连接 DriverManager.getConnection(url,usr,password,)

3.获取数据库操作对象 createStatement

4.执行SQL语句（executeUpdate（sql）和executeQuery（sql））

5.处理查询结果集 while循环

5.释放资源（从小到大）

import java.sql.*;
import java.util.ResourceBundle;

public class JDBCDEMO {
    public static void main(String[] args) {
        Connection con=null;
        Statement st=null;
        //使用资源绑定器，先拿出properties
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc"); //不用带后缀
        String driver = bundle.getString("driver");
        String url = bundle.getString("url");
        String usr = bundle.getString("usr");
        String password = bundle.getString("password");


        //1.注册驱动
        try {
//            (1) Class.forName("com.mysql.jdbc.Driver");
//            (2)DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            Class.forName(driver);



        //2.获取连接
//            String url="jdbc:mysql://127.0.0.1:3306/bjpowernode";
//            String usr="root";
//            String password="123456";
//          Connection con = DriverManager.getConnection(url, usr, password);   释放资源需要分开写
            con = DriverManager.getConnection(url, usr, password);

        //3.创建数据库对象
//            Statement st = con.createStatement();   //释放资源需要分开写
            st = con.createStatement();
            
        //4.执行SQL语句
            //DML
//            String sql="INSERT into dept(deptno,dname,loc) VALUES (50,'sdsds','uuuuuuuu') ";
//            int conut = st.executeUpdate(sql);  //返回值是一个整形表示执行成功几条语句

            //DQL
            String sql="select ename,empno,sal from emp";
            ResultSet rs = st.executeQuery(sql);

        //5.处理查询结果集
            while (rs.next()){
                //按下标取出，程序不健壮
//                String ename = rs.getString(1);
//                String empno = rs.getString(2);
//                String sal = rs.getString(3);

                String ename = rs.getString("ename");
                int empno = rs.getInt("empno");   //可以全用String类型，但也可以使用固定的格式
                double sal = rs.getDouble("sal");
                System.out.println(ename+" "+empno+" "+sal); //如果是Int型等等可以做运算，例如（sal+100）
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
        //6.释放资源从小到大
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

 

JDBC查询，增删改进行封装操作：

public class JdbcUtilTest {
    public static void main(String[] args) throws SQLException {

//        String sql="insert into user(id,username,password) values (1003,'test','test') ";
//        Connection con = MysqlUtil.getCon();
//        PreparedStatement ps = MysqlUtil.getPS(sql);
//        int i = MysqlUtil.getUpdate();
//        System.out.println(i);
//        MysqlUtil.close();

        String sql="select * from user";
        Connection con = MysqlUtil.getCon();
        PreparedStatement ps = MysqlUtil.getPS(sql);
        ResultSet rs = MysqlUtil.getSelect();
        while (rs.next()){
            System.out.println(rs.getString(1));
            System.out.println(rs.getString(2));
            System.out.println(rs.getString(3));
        }
        
    }
}

import java.sql.*;

public class MysqlUtil {
    static Connection con=null;
    static PreparedStatement ps=null;
    static ResultSet rs=null;
//
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getCon()  {
        try {
            con = DriverManager.getConnection("jdbc:mysql://master:3306/bjpowernode", "root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    public static PreparedStatement getPS(String sql)  {
        try {
            ps = con.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  ps;
    }

    //查
    public static ResultSet getSelect()  {
        try {
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    //增删改
    public static int getUpdate()  {
        int i = 0;
        try {
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public static void close()  {
        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

 

SQL注入问题：

sql = SELECT * FROM 用户表 WHERE NAME = 用户输入的用户名 AND PASSWORD = 用户输的密码;

此时如果用户输入账号为 XXX ，密码为 XXX ' or'  a' ='   a' 此时变可以登录成功，这时需要用PreparedStatement来解决