关闭

SQL增删改查、SQL注入、事务

标签: java反射mysql
217人阅读 评论(0) 收藏 举报
分类:

SQL注入:
SELECT * FROM user WHERE name=”abcd” and password=”“;
当password设置为1234” or “1”=”1”;
SELECT * FROM user WHERE name=”abcd” and password=”1234” or “1”=”1”;
所以在JDBC中,使用?替代参数

事务写法:

将获取到的连接的自动提交关闭就OK
Connection con=...;
con.setAutoConnit(false);
//操作完成之后再提交
con.commit;

JavaBean建立

public class Student {
    private Integer Id;
    private String Name;
    private Integer Cno;

    public Integer getId() {
        return Id;
    }

    public void setId(Integer id) {
        Id = id;
    }

    public String getName() {
        return Name;
    }

    public void setName(String name) {
        Name = name;
    }

    public Integer getCno() {
        return Cno;
    }

    public void setCno(Integer cno) {
        Cno = cno;
    }

    @Override
    public String toString() {
        return "Student [Id=" + Id + ", Name=" + Name + ", Cno=" + Cno + "]";
    }
}

数据库资源封装:

public class DataSource {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://192.168.216.3:3306/eclipse?useUnicode=true&characterEncoding=utf8";
    public static final String USER = "root";
    public static final String PASSWORD = "123456";
}
//我的数据库在虚拟机里,端口号和账号密码如上

增删改查封装:

public class jdbcutil {
    public static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName(util.DataSource.DRIVER);
            con = DriverManager.getConnection(util.DataSource.URL, util.DataSource.USER, util.DataSource.PASSWORD);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return con;
    }

    public static void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) {
        try {
            if (rs != null)
                rs.close();
            if (rs != null)
                pstmt.close();
            if (rs != null)
                con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static int executeUpdate(String sql, Object... params) {
        int result = 0;
        Connection con = getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = con.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            util.jdbcutil.closeAll(null, pstmt, con);
        }
        return result;
    }

    public static <T> List<T> executeQuery(String sql, RowMap<T> rowmap, Object... params) {
        Connection con = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<T> result = new ArrayList<>();
        try {
            pstmt = con.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                T t = rowmap.RowMapping(rs);
                result.add(t);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            closeAll(rs, pstmt, con);
        }
        return result;
    }
}

接口定义:

public interface RowMap<T> {
    public T RowMapping(ResultSet rs);
}
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:13326次
    • 积分:313
    • 等级:
    • 排名:千里之外
    • 原创:17篇
    • 转载:2篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档