JDBC工具类及增删改查

原创 2018年04月15日 20:02:31
  • 数据库:
CREATE DATABASE mydb;
#使用数据库
USE mydb;
###创建分类表
CREATE TABLE category(
  cid INT PRIMARY KEY AUTO_INCREMENT  ,
  cname VARCHAR(100)
);
#初始化数据
INSERT INTO category (cname) VALUES('家电');
INSERT INTO category (cname) VALUES('服饰');
INSERT INTO category (cname) VALUES('化妆品');
  • 工具类:注册驱动,获得连接,释放资源
package bull01.JDBCBasis;

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

public class JDBCUtils {
    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/mydb";
    private static String user = "root";
    private static String password = "root";

    //注册驱动
    static {
        try {
            Class.forName(driver);
        } catch (Exception e) {
            //抛去运行时期异常
            throw new RuntimeException(e);
        }
    }

    //获得连接
    public static Connection getConnections() throws SQLException {
        Connection conn = DriverManager.getConnection(url,user,password);
        return conn;
    }

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

            };
        }

        if(st != null) {
            try {
                st.close();
            } catch (SQLException e) {
            }
        }

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

  • JDBC增删改查:
package bull02.JDBCUtils;

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

import org.junit.Test;

/*
 * 数据库增删改查
 */
public class CRUDDemo {
    @Test
    public void demo1() {
        //添加
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            int r = st.executeUpdate("insert into category (cname) values ('测试')");
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);

        } finally {
            JDBCUtils.closeResource(conn, st, rs);
        }
    }

    @Test
    public void demo2() {
        //删除
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            int r = st.executeUpdate("delete from category where cid = 7");
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, st, rs);
        }
    }

    @Test
    public void demo3() {
        //修改
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            int r = st.executeUpdate("update category set cname = '食品' where cid = 3");
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, st, rs);
        }
    }

    @Test
    public void demo4() {
        //查询
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获得语句执行者
            st = conn.createStatement();
            //执行SQL语句
            rs = st.executeQuery("select * from category");

            while(rs.next()) {
                int cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                System.out.println(cid + "---" + cname);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
  • 采用预处理防SQL注入
package bull02.JDBCUtils;
/*
 * 防SQL注入问题
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

public class PrepareDemo {
    @Test
    public void demo1() {
        //添加
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //处理SQL语句
            String sql = "insert into category (cid,cname) values (?,?)";
            //获得预处理对象
            psmt = conn.prepareStatement(sql);
            //设置实际参数
            psmt.setInt(1, 4);
            psmt.setString(2, "书本");
            //执行,返回的r是数据库改变的行数
            int r = psmt.executeUpdate();
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, psmt, rs);
        }
    }

    @Test 
    public void demo2() {
        //删除
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获取sql语句
            String sql = "delete from category where cid = ?";
            //获得预处理对象
            psmt = conn.prepareStatement(sql);
            //设置实际参数
            psmt.setInt(1, 8);
            //执行
            int r = psmt.executeUpdate();
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, psmt, rs);
        }
    }

    @Test
    public void demo3() {
        //修改
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获得SQL语句
            String sql = "update category set cname = ? where cid = ?";
            //获得预处理对象
            psmt = conn.prepareStatement(sql);
            //设置实际参数
            psmt.setString(1, "汽车");
            psmt.setInt(2, 4);
            //执行
            int r = psmt.executeUpdate();
            System.out.println(r);
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            //释放资源
            JDBCUtils.closeResource(conn, psmt, rs);
        }

    }

    @Test
    public void demo4() {
        //全部查询
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获得SQL语句
            String sql = "select * from category";
            //获得预处理对象
            psmt = conn.prepareStatement(sql);
            //设计实际参数,这里没有参数可以设置

            //执行
            rs = psmt.executeQuery();
            while(rs.next()) {
                Integer cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                System.out.println(cid + "---" + cname);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, psmt, rs);
        }
    }

    @Test
    public void demo5() {
        //通过id查询
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获得SQL语句
            String sql = "select * from category where cid = ?";
            //获得预处理对象
            psmt = conn.prepareStatement(sql);
            //设置实际参数
            psmt.setInt(1, 1);
            //执行
            rs = psmt.executeQuery();
            if(rs.next()) {
                Integer cid = rs.getInt("cid");
                String cname = rs.getString("cname");
                System.out.println(cid + "---" + cname);
            }
            else {
                System.out.println("查询不到");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, psmt, rs);
        }
    }
}

采用预处理防SQL注入案例

  • 数据库:
USE mydb;
CREATE TABLE USER(
  NAME VARCHAR(50) PRIMARY KEY,
  passwd VARCHAR(50)
);

INSERT INTO USER (NAME,passwd) VALUES ('jack','123456');
  • PreparedDemo:
package bull02.JDBCUtils;

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

import org.junit.Test;

public class PreparedDemo {
    @Test
    public void method() {
        String name = "jack #";
        String passwd = "123456";

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            //获得连接
            conn = JDBCUtils.getConnections();
            //获取SQL语句
            String sql = "select * from user where name = ? and passwd = ?";
            //获得预处理对象
            psmt = conn.prepareStatement(sql);
            //设置实际参数
            psmt.setString(1, name);
            psmt.setString(2, passwd);
            //执行
            rs = psmt.executeQuery();

            if(rs.next()) {
                System.out.println("登录成功!");
            }
            else {
                System.out.println("登录失败!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.closeResource(conn, psmt, rs);
        }
    }
}
版权声明:本文为博主原创文章,转载请注明出处。 https://blog.csdn.net/sinat_40662281/article/details/79952476

JDBC基础视频课程

了解JDBC的概念和 必要性 了解JDBC驱动程序类型 了解java.sql包 使用JDBC进行 数据库编程 PreparedStatement接口 了解事务
  • 2016年10月13日 14:20

jdbc增删改查的工具类

package com.yanshu.util; import java.sql.Connection; import java.sql.PreparedStatement; impor...
  • ruiguang21
  • ruiguang21
  • 2017-09-03 18:29:23
  • 168

MySQL数据库学习笔记(十一)----DAO设计模式实现数据库的增删改查(进一步封装JDBC工具类)

系列文章并非本人原创。 生命壹号:http://www.cnblogs.com/smyhvae/ 文章来源:http://www.cnblogs.com/smyhvae/p/4059514.h...
  • li12412414
  • li12412414
  • 2016-07-29 09:53:40
  • 1580

jdbc进行简单的增删改查

http://www.cnblogs.com/wuyuegb2312/p/3872607.html#tittle35  对MySQL进行操作,这时下面的import就不会报错了: ...
  • u012510757
  • u012510757
  • 2017-02-15 21:39:51
  • 1142

JDBC增删改查实例

1、在mysql中创建数据库  users  (Integer id,String name, String pass,String sex,Integer age); 2、分层实现: cn.cs...
  • jjaze3344
  • jjaze3344
  • 2011-11-14 21:05:56
  • 13202

JDBC增删改查简单例子

  • 2017年04月12日 16:37
  • 901KB
  • 下载

简单的JDBC的增删改查操作,附源码

  • 2017年07月04日 22:39
  • 515KB
  • 下载

【java基础】JDBC实现增删改查

梳理知识:JDBC的使用 准备工作: jdbc连接的jar包,可到mysql官网下载或者百度安装好mysql数据库,并创建数据库和表java编写操作数据库的代码 代码实现增删改查如下: packa...
  • SolitudeSky
  • SolitudeSky
  • 2017-06-01 08:43:04
  • 939

JDBC(三)JDBC实现增删改查(CRUD)

jdbc主要用在JAVA程序和数据库打交道,最基础简单的事情就是对数据的增删改查。对数据库中的user表进行增删改查JdbcUtils.java:package com.aaa.utils;impor...
  • qq_25201665
  • qq_25201665
  • 2016-08-12 16:37:56
  • 4635

java十分经典的jdbc增删改查程序,含sql语句

  • 2013年04月23日 08:41
  • 10KB
  • 下载
收藏助手
不良信息举报
您举报文章:JDBC工具类及增删改查
举报原因:
原因补充:

(最多只允许输入30个字)