用jdbc与数据库建立连接
1.在idea中配置mysql-connector的驱动
MySQL :: Download MySQL Connector/J (Archived Versions)
在官网下载压缩包,将其中的jar包放到测试项目的lib库中
也可以新建一个lib目录,将该文件夹变为项目的依赖库
将该jar包复制进去
2.用jdbc与数据库建立连接
为方便后续的调用,将数据库的相关信息写在配置文件(jdbc.properties)中
文件目录如下:
jdbc.properties的内容
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/how2java?useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=hyj011215
将test2写成工具类,实现连接
public class test2 {
//此处定义可以提升作用域
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try{
//读取配置文件
InputStream in = test2.class.getClassLoader().getResourceAsStream("jdbc.properties");
//得到一个输入流
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//驱动只加载一次(加载驱动可能出错,需要抛出异常)
Class.forName(driver);
}catch(Exception e){
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接资源
public static void release(Connection conn, Statement st , ResultSet rs){
if (rs!=null){
try{
rs.close();
}catch(Exception e){
e.printStackTrace();
}
}
if (st!=null){
try{
st.close();
}catch(Exception e){
e.printStackTrace();
}
}
if (conn!=null){
try{
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
}
3.调用工具类,实现增删改查
增加
public class testinsert {
public static void main(String[] args) {
//获取数据库的连接(在test2中已经加载数据库的驱动)
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection = test2.getConnection();
statement = connection.createStatement();
String sql = "INSERT into category_(`id`,`name`) VALUES(6,'category6');";
int num = statement.executeUpdate(sql);
if (num>0){
System.out.println("成功插入一条数据");
}
}catch(Exception e){
e.printStackTrace();
}finally {
test2.release(connection,statement,resultSet);
}
}
}
删除
public class testDelete {
public static void main(String[] args) {
//获取数据库的连接(在test2中已经加载数据库的驱动)
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection = test2.getConnection();
statement = connection.createStatement();
String sql = "delete from category_ where id=6;";
int num = statement.executeUpdate(sql);
if (num>0){
System.out.println("成功删除一条数据");
}
}catch(Exception e){
e.printStackTrace();
}finally {
test2.release(connection,statement,resultSet);
}
}
}
修改
public class testUpdate {
public static void main(String[] args) {
//获取数据库的连接(在test2中已经加载数据库的驱动)
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection = test2.getConnection();
statement = connection.createStatement();
String sql = "UPDATE category_ SET name='hyjcategory' where id=5;";
int num = statement.executeUpdate(sql);
if (num>0){
System.out.println("成功更新一条数据");
}
}catch(Exception e){
e.printStackTrace();
}finally {
test2.release(connection,statement,resultSet);
}
}
}
查询
public class testSelect {
public static void main(String[] args) {
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection=test2.getConnection();
statement=connection.createStatement();
String sql = "select * from category_ where id=5";
resultSet=statement.executeQuery(sql);
//注意此处调用的executeQuery的返回值与上述操作的返回值不同,上述操作的返回值为int,而此处为一个对象
while (resultSet.next()){
System.out.println( resultSet.getString("name"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
test2.release(connection,statement,resultSet);
}
}
}
4.SQL注入的试验
模拟类似账号密码的匹配(此处采用id和name字段的匹配),通过构造如“1=1”的恒等式使得sql查询语句的条件恒成立,从而获得所有的查询数据。如SELECT * FROM category_ WHERE id = ‘’ or ‘1=1’AND name=’'or ‘1=1’;
测试代码如下
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
//拼接处id=""或者一个恒成立条件的sql语句来满足查询条件,从而获得数据
login("' or '1=1", "'or '1=1");
}
public static void login(String id,String name){
Connection connection=null;
Statement statement=null;
ResultSet resultSet=null;
try {
connection=test2.getConnection();
statement=connection.createStatement();
String sql="SELECT * FROM category_ WHERE id = '" +id+"'AND name='"+name+"';";
System.out.println(sql);
resultSet=statement.executeQuery(sql);
while(resultSet.next()){
System.out.println(resultSet.getInt("id")+" "+resultSet.getString("name"));
}
}catch(Exception e)
{
e.printStackTrace();
}finally{
test2.release(connection,statement,resultSet);
}
}
}