JDBC 利用propertise简单例子

1、db.propertise文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school
username=root
password=123456

 

 

2、JdbcUtil类文件

package JDBC_Test;

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

public class JdbcUtils {

    private static String strDriver   = null;
    private static String strUrl      = null;
    private static String strUserName = null;
    private static String strPassword = null;

    static{
        try {
            InputStream in =  JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

            Properties properties = new Properties();
            properties.load(in);

            strDriver   = properties.getProperty("driver");
            strUrl      = properties.getProperty("url");
            strUserName = properties.getProperty("username");
            strPassword = properties.getProperty("password");

            Class.forName(strDriver);



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


    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(strUrl, strUserName, strPassword);
    }

    //释放资源
    public static void release(Connection conn, Statement state, ResultSet rs){
        if(null != rs){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

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

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

3、测试类

package JDBC_Test;

import com.sun.scenario.effect.impl.sw.java.JSWBlend_SRC_OUTPeer;

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

import static JDBC_Test.JdbcUtils.*;

public class demo2 {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();//获取连接
            statement = connection.createStatement();

            //?useUnicode=true&characterEncoding=utf8&useSSL=true


            //增加语句
            String strInsertSql = "INSERT INTO student(`studentno`, `loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)\n" +
                    "VALUES(1009,'123456','ke',1,3,'123456789','金华','1994-12-16','2123456@qq.com','1324546878454')";

            //删除语句
            String strDeleteSql = "DELETE FROM student WHERE `studentname`='aaa';";

            //更新语句
            string strUpdateSql = "UPDATE student \n" +
                    "SET  `studentname`='bbb', `sex`=1\n" +
                    "WHERE `studentno`=1009 \n";

            //查询语句
            String strQuerySql = "SELECT * FROM student \n" +
                    "WHERE `studentname`='ke'";

            int iRow = statement.executeUpdate(strInsertSql);
            if (iRow > 0) {
                System.out.println("插入成功");
            }
bbb
            resultSet = statement.executeQuery(strQuerySql);
            while (resultSet.next()) {
                System.out.println("id= " + resultSet.getObject("studentno"));
                System.out.println("name= " + resultSet.getObject("studentname"));
                System.out.println("sex= " + resultSet.getObject("sex"));
                System.out.println("gradeid= " + resultSet.getObject("gradeid"));
                System.out.println("phone= " + resultSet.getObject("phone"));
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }

}

 

JDBC事务操作

package JDBC_Test;

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

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

        Connection connection = null;
        PreparedStatement preSt = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            connection.setAutoCommit(false);//取消自动提交 并且开始事务

            String strSql1 = "UPDATE bank SET money=money + 100 WHERE `name`='A';";
            preSt = connection.prepareStatement(strSql1);
            int iRow1 = preSt.executeUpdate();
            if(iRow1 <= 0){
                System.out.println("strSql1执行失败，iRow1：" + iRow1);
            }

            String strSql2 = "UPDATE bank SET money=money - 100 WHERE `name`='B'";
            preSt = connection.prepareStatement(strSql2);
            int iRow2 = preSt.executeUpdate();
            if(iRow2 <= 0){
                System.out.println("strSql2执行失败，iRow2：" + iRow2);
            }

            connection.commit();
            System.out.println("转账成功");

            String strSelect = "select * from bank";
            preSt = connection.prepareStatement(strSelect);
            resultSet = preSt.executeQuery();
            while (resultSet.next()){
                System.out.println("name: " + resultSet.getObject("name") + "money: " + resultSet.getObject("money"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            JdbcUtils.release(connection,preSt,resultSet);
        }

    }
}

JDBC_PerpareStatement 安全的方法（防止注入攻击）

package JDBC_Test;

import java.sql.*;
import java.util.Date;

public class JDBC_PerpareStatement {

    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String strInsertSql = "INSERT INTO result(`studentno`,`subjectno`,`examdate`,`studentresult`) VALUES(?,?,?,?)";

            preparedStatement = connection.prepareStatement(strInsertSql);

            preparedStatement.setInt(1, 1006);
            preparedStatement.setInt(2, 3);
            preparedStatement.setDate(3, new java.sql.Date(new Date().getTime()));
            preparedStatement.setInt(4, 77);

            int iRow = preparedStatement.executeUpdate();
            if(iRow > 0){
                System.out.println("perpareStatement mysql 执行成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}