JDBC - 基础篇-阿里云开发者社区

Ps：连接本地简写：jdbc:mysql:///jdbc。

Ps：ResultSet光标最初位于结果集的第一行的前面，即空数据位置。 Ps：.close(); 代表被释放掉了，但未必马上被垃圾回收掉，所以最后加个 conn=null; 确保尽可能早的被回收，做到真正意义上的释放。

Ps：这种方式不推荐，因为当项目发布到Web服务器中时，并没有src目录。

packagecom.imooc.jdbc.demo2;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.Statement;
importorg.junit.Test;
importcom.imooc.jdbc.utils.JDBCUtils;
/*** 演示JDBC的注入的漏洞* @author jt**/publicclassJDBCDemo4 {
@Test/*** 测试SQL注入漏洞的方法*/publicvoiddemo1(){
booleanflag=JDBCDemo4.login2("aaa' or '1=1", "1fsdsdfsdf");
//      boolean flag = JDBCDemo4.login2("aaa' -- ", "1fsdsdfsdf"); // “--” 把后面的sql代码注释掉if(flag==true){
System.out.println("登录成功！");
        }else{
System.out.println("登录失败！");
        }
    }
/*** 避免SQL注入漏洞的方法*/publicstaticbooleanlogin2(Stringusername,Stringpassword){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
booleanflag=false;
try{
// 获得连接:conn=JDBCUtils.getConnection();
// 编写SQL:Stringsql="select * from user where username = ? and password = ?";
// 预处理SQL:pstmt=conn.prepareStatement(sql);
// 设置参数:pstmt.setString(1, username);
pstmt.setString(2, password);
// 执行SQL：rs=pstmt.executeQuery();
// 判断结果街if(rs.next()){
flag=true;
            }else{
flag=false;
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils.release(rs, pstmt, conn);
        }
returnflag;
    }
/*** 产生SQL注入漏洞的方法* @param username* @param password* @return*/publicstaticbooleanlogin(Stringusername,Stringpassword){
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
booleanflag=false;
try{
conn=JDBCUtils.getConnection();
// 创建执行SQL语句的对象:stmt=conn.createStatement();
// 编写SQL:Stringsql="select * from user where username = '"+username+"' and password = '"+password+"'";
// 执行SQL:rs=stmt.executeQuery(sql);
// 判断结果集中是否有数据。if(rs.next()){
flag=true;
            }else{
flag=false;
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils.release(rs, stmt, conn);
        }
returnflag;
    }
}

packagecom.imooc.jdbc.demo2;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importorg.junit.Test;
importcom.imooc.jdbc.utils.JDBCUtils;
/*** PreparedStatement的使用* @author jt**/publicclassJDBCDemo5 {
@Test/*** 查询一条记录*/publicvoiddemo5(){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
try{
// 获得连接:conn=JDBCUtils.getConnection();
// 编写SQL：Stringsql="select * from user where uid = ?";
// 预编译SQL:pstmt=conn.prepareStatement(sql);
// 设置参数:pstmt.setObject(1, 3);
// 执行SQL:rs=pstmt.executeQuery();
// 判断结果集:if(rs.next()){
System.out.println(rs.getInt("uid")+"  "+rs.getString("username")+"  "+rs.getString("password")+"  "+rs.getString("name"));
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils.release(rs, pstmt, conn);
        }
    }
@Test/*** 查询所有数据*/publicvoiddemo4(){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
try{
// 获得连接:conn=JDBCUtils.getConnection();
// 编写SQL:Stringsql="select * from user";
// 预编译SQL:pstmt=conn.prepareStatement(sql);
// 设置参数// 执行SQL:rs=pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("uid")+"  "+rs.getString("username")+"  "+rs.getString("password")+"  "+rs.getString("name"));
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils.release(rs, pstmt, conn);
        }
    }
@Test/*** 删除数据*/publicvoiddemo3(){
Connectionconn=null;
PreparedStatementpstmt=null;
try{
// 获得连接:conn=JDBCUtils.getConnection();
// 编写SQL：Stringsql="delete from user where uid = ?";
// 预编译SQL:pstmt=conn.prepareStatement(sql);
// 设置参数:pstmt.setInt(1, 6);
// 执行SQL:intnum=pstmt.executeUpdate();
if(num>0){
System.out.println("删除成功！");
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils.release(pstmt, conn);
        }
    }
@Test/*** 修改数据*/publicvoiddemo2(){
Connectionconn=null;
PreparedStatementpstmt=null;
try{
// 获得连接:conn=JDBCUtils.getConnection();
// 编写SQL:Stringsql="update user set username = ?,password = ?,name = ? where uid = ?";
// 预编译SQL:pstmt=conn.prepareStatement(sql);
// 设置参数:pstmt.setString(1, "www");
pstmt.setString(2, "123456");
pstmt.setString(3, "张六");
pstmt.setInt(4, 6);
// 执行SQL:intnum=pstmt.executeUpdate();
if(num>0){
System.out.println("修改成功！");
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils.release(pstmt, conn);
        }
    }
@Test/*** 保存数据*/publicvoiddemo1(){
Connectionconn=null;
PreparedStatementpstmt=null;
try{
// 获得连接:conn=JDBCUtils.getConnection();
// 编写SQL:Stringsql="insert into user values (null,?,?,?)";
// 预处理SQL:pstmt=conn.prepareStatement(sql);
// 设置参数的值:pstmt.setString(1, "qqq");
pstmt.setString(2, "123");
pstmt.setString(3, "张武");
// 执行SQL:intnum=pstmt.executeUpdate();
if(num>0){
System.out.println("保存成功！");
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
// 释放资源JDBCUtils.release(pstmt, conn);
        }
    }
}

packagecom.imooc.jdbc.demo3;
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importorg.junit.Test;
importcom.imooc.jdbc.utils.JDBCUtils;
importcom.imooc.jdbc.utils.JDBCUtils2;
importcom.mchange.v2.c3p0.ComboPooledDataSource;
/*** 连接池的测试类* @author jt**/publicclassDataSourceDemo1 {
@Test/*** 使用配置文件的方式*/publicvoiddemo2(){
Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
try{
// 获得连接:// ComboPooledDataSource dataSource = new ComboPooledDataSource();// 获得连接:// conn = dataSource.getConnection();conn=JDBCUtils2.getConnection();
// 编写Sql:Stringsql="select * from user";
// 预编译SQL:pstmt=conn.prepareStatement(sql);
// 设置参数// 执行SQL:rs=pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils2.release(rs, pstmt, conn);
        }
    }
@Test/*** 手动设置连接池*/publicvoiddemo1(){
// 获得连接:Connectionconn=null;
PreparedStatementpstmt=null;
ResultSetrs=null;
try{
// 创建连接池:ComboPooledDataSourcedataSource=newComboPooledDataSource();
// 设置连接池的参数:dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///jdbctest");
dataSource.setUser("root");
dataSource.setPassword("abc");
dataSource.setMaxPoolSize(20);
dataSource.setInitialPoolSize(3);
// 获得连接:conn=dataSource.getConnection();
// 编写Sql:Stringsql="select * from user";
// 预编译SQL:pstmt=conn.prepareStatement(sql);
// 设置参数// 执行SQL:rs=pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("name"));
            }
        }catch(Exceptione){
e.printStackTrace();
        }finally{
JDBCUtils.release(rs, pstmt, conn);
        }
    }
}

公共代码块：

// 针对非连接池packagecom.imooc.jdbc.utils;
importjava.io.IOException;
importjava.io.InputStream;
importjava.sql.Connection;
importjava.sql.DriverManager;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.Properties;
/*** JDBC的工具类* @author jt**/publicclassJDBCUtils {
privatestaticfinalStringdriverClass;
privatestaticfinalStringurl;
privatestaticfinalStringusername;
privatestaticfinalStringpassword;
static{
// 加载属性文件并解析：Propertiesprops=newProperties();
// 如何获得属性文件的输入流？// 通常情况下使用类的加载器的方式进行获取：InputStreamis=JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
props.load(is);
        } catch (IOExceptione) {
e.printStackTrace();
        }
driverClass=props.getProperty("driverClass");
url=props.getProperty("url");
username=props.getProperty("username");
password=props.getProperty("password");
    }
/*** 注册驱动的方法* @throws ClassNotFoundException */publicstaticvoidloadDriver() throwsClassNotFoundException{
Class.forName(driverClass);
    }
/*** 获得连接的方法:* @throws SQLException */publicstaticConnectiongetConnection() throwsException{
loadDriver();
Connectionconn=DriverManager.getConnection(url, username, password);
returnconn;
    }
/*** 资源释放*/publicstaticvoidrelease(Statementstmt,Connectionconn){
if(stmt!=null){
try {
stmt.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
stmt=null;
        }
if(conn!=null){
try {
conn.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
conn=null;
        }
    }
publicstaticvoidrelease(ResultSetrs,Statementstmt,Connectionconn){
if(rs!=null){
try {
rs.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
rs=null;
        }
if(stmt!=null){
try {
stmt.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
stmt=null;
        }
if(conn!=null){
try {
conn.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
conn=null;
        }
    }
}

// 针对连接池packagecom.imooc.jdbc.utils;
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importcom.mchange.v2.c3p0.ComboPooledDataSource;
/*** JDBC的工具类* @author jt**/publicclassJDBCUtils2 {
privatestaticfinalComboPooledDataSourcedataSource=newComboPooledDataSource();
/*** 获得连接的方法:* @throws SQLException */publicstaticConnectiongetConnection() throwsException{
Connectionconn=dataSource.getConnection();
returnconn;
    }
/*** 资源释放*/publicstaticvoidrelease(Statementstmt,Connectionconn){
if(stmt!=null){
try {
stmt.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
stmt=null;
        }
if(conn!=null){
try {
conn.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
conn=null;
        }
    }
publicstaticvoidrelease(ResultSetrs,Statementstmt,Connectionconn){
if(rs!=null){
try {
rs.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
rs=null;
        }
if(stmt!=null){
try {
stmt.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
stmt=null;
        }
if(conn!=null){
try {
conn.close();
            } catch (SQLExceptione) {
e.printStackTrace();
            }
conn=null;
        }
    }
}

<?xmlversion="1.0"encoding="UTF-8"?><c3p0-config><default-config><propertyname="driverClass">com.mysql.jdbc.Driver</property><propertyname="jdbcUrl">jdbc:mysql:///jdbctest</property><propertyname="user">root</property><propertyname="password">abc</property><propertyname="initialPoolSize">5</property><propertyname="maxPoolSize">20</property></default-config></c3p0-config>

driverClass=com.mysql.jdbc.Driverurl=jdbc:mysql:///jdbctestusername=rootpassword=abc

JDBC - 基础篇

公共代码块：

热门文章

最新文章

相关课程

相关电子书