jdbc_相关代码

一、statement执行sql语句（）

(1)工具类

package Zer.demo.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {

    public static String driver;
    public static String url;
    public static String root;
    public static String password;



    static {
        try {
            //进行读取资源,配置文件的
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            //实例化配置文件对象拿取资源
            Properties properties = new Properties();
            //去加载读取出来的资源
            properties.load(in);

            //将资源读取出来储存
             driver = properties.getProperty("driver");
             url = properties.getProperty("url");
             root = properties.getProperty("root");
             password = properties.getProperty("password");

             //1.加载驱动
             Class.forName(driver);

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

    }

    //2.获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, root, password);
    }
    //3.释放资源
    public static void rslease(Connection coon, Statement st, ResultSet resultSet){

        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        
        if (st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (coon!=null){
            try {
                coon.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }



    }
}

---

(2)配置文件

名称：db.Properties,自己在new中新建一个，图标如下面

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
root=root
password=123456

---

1.删除

package Zer.demo.Caozuo;

import Zer.demo.util.JdbcUtils;

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

public class Delete {
    public static void main(String[] args) {

        Connection coon = null;
        Statement st = null;
        ResultSet resultSet = null;

        try {
            //调用了此方法，此时静态代码块会自动执行。加载驱动已经执行了
            coon =  JdbcUtils.getConnection();
            st = coon.createStatement();
            String sql ="delete from grade where gradeid=25";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.rslease(coon,st,resultSet);
        }
    }


}

---

2.更新

package Zer.demo.Caozuo;

import Zer.demo.util.JdbcUtils;

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

public class Update {
    public static void main(String[] args) {

        Connection coon = null;
        Statement st = null;
        ResultSet resultSet = null;

        try {
            //调用了此方法，此时静态代码块会自动执行。加载驱动已经执行了
            coon =  JdbcUtils.getConnection();
            st = coon.createStatement();
            String sql ="update grade set gradename='大家都一样，即使不一样，累一点也无妨' where gradeid= 22";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.rslease(coon,st,resultSet);
        }
    }


}

---

3.插入

package Zer.demo.Caozuo;

import Zer.demo.util.JdbcUtils;

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

public class Insert {
    public static void main(String[] args) {

        Connection coon = null;
        Statement st = null;
        ResultSet resultSet = null;

        try {
            //调用了此方法，此时静态代码块会自动执行。加载驱动已经执行了
           coon =  JdbcUtils.getConnection();
           st = coon.createStatement();
           String sql ="insert into grade values(25,'没有多余失落可以走')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.rslease(coon,st,resultSet);
        }
        }


}

---

4.查询

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

public class Select {
    public static void main(String[] args) {

        Connection coon = null;
        Statement st = null;
        ResultSet resultSet = null;

        try {
            //调用了此方法，此时静态代码块会自动执行。加载驱动已经执行了
            coon =  JdbcUtils.getConnection();
            st = coon.createStatement();
            String sql= "SELECT * FROM grade";
            //5、用创建的statement对象执行sql语句，返回结果集
            resultSet = st.executeQuery(sql);

            while (resultSet.next()){
                System.out.println("gradeid="+resultSet.getObject("gradeid"));
                System.out.println("gradename="+resultSet.getObject("gradename"));
                System.out.println("………………………………………………………………………………………………………………………………………");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.rslease(coon,st,resultSet);
        }
    }
    
}

如果用statement虽然容易理解上手，但是有SQL注入问题，容易出现安全隐患，为此推荐用preparestatement

---

5、SQL注入问题

stament存在SQL注入问题

package Zer.demo.util;

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

public class Zhuru {
    public static void main(String[] args) {
        Login(14," 'or '1=1");
    }

    //业务语句
    public static void Login(int password11,String yonghuming){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st= conn.createStatement();
            String sql = "select * from grade where gradeid ='"+password11+"' and  gradename='"+yonghuming+"'";

            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("gradename"));
                System.out.println(rs.getInt("gradeid"));
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.rslease(conn,st,rs);
        }

    }

}

二、Preparedstatement执行sql语句

(1)工具类

package Zer.demo.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils1 {

    public static String driver = null;
    public static String url = null;
    public static String root = null;
    public static String password = null;

    static {
        try {
        InputStream in = JdbcUtils1.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(in);

        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        root = properties.getProperty("root");
        password = properties.getProperty("password");

        Class.forName(driver);


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

    }

    public static Connection getconnection() throws SQLException {
        return DriverManager.getConnection(url,root,password);
    }

    public static void  release(Connection conn, PreparedStatement pr, ResultSet rs){

        if (rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        if (pr!=null){
            try {
                pr.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }



    }


}

(2)配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
root=root
password=123456

---

1、删除

package Zer.demo.PrepareStatement;

import Zer.demo.util.JdbcUtils1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Delete1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils1.getconnection();
            String sql = "delete from grade where gradeid = ?";//先写SQL语句，‘？’是占位符号
            ps = conn.prepareStatement(sql);//进行预编译，不执行
            //手动执行SQL语句
            ps.setInt(1, 26);
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("删除成功");
            } else {
                System.out.println("删除失败");
            }

        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils1.release(conn, ps, rs);
        }
    }
}

---

## 2、更新

package Zer.demo.PrepareStatement;

import Zer.demo.util.JdbcUtils1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Update1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils1.getconnection();
            String sql = "update grade set  gradename = ? where gradeid = ?";//先写SQL语句，‘？’是占位符号
            ps = conn.prepareStatement(sql);//进行预编译，不执行
            //手动执行SQL语句

            ps.setString(1, "女子之道，学会自我爱护");
            ps.setInt(2, 26);
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("更新成功");
            } else {
                System.out.println("更新失败");
            }

        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils1.release(conn, ps, rs);
        }
    }
}

---

3、插入

package Zer.demo.PrepareStatement;

import Zer.demo.util.JdbcUtils1;

import java.sql.*;

public class Insert1 {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils1.getconnection();
            String sql = "insert into grade(gradeid,gradename) values(?,?)";//先写SQL语句，‘？’是占位符号
            ps = conn.prepareStatement(sql);//进行预编译，不执行
            //手动执行SQL语句
            ps.setInt(1, 26);//第一个占位符，赋值26
            ps.setString(2, "君子之道，自我修养");//第二个占位符，赋值君子之道，自我修养
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功");
            } else {
                System.out.println("插入失败");
            }

        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils1.release(conn, ps, rs);
        }
    }
}

---

4、查询

package Zer.demo.PrepareStatement;

import Zer.demo.util.JdbcUtils1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Select1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils1.getconnection();
            String sql = "select * from grade";
            String sql1 = "select * from grade";
            ps = conn.prepareStatement(sql);//进行预编译，不执行
            //手动执行SQL语句,没有占位符就可以不用写

            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt("gradeid"));
                System.out.println(rs.getString("gradename"));
            }

        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils1.release(conn, ps, rs);
        }
    }
}