JDBC

Java Database Connectivty java数据库链接

基本链接

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * 
 * @author mephisto
 *
 */
public class Dmeo1 {

	public static void main(String[] args) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		try {
			// 1.注册驱动
			DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());

			// 2.建立连接
			/*
			 * Establishing SSL connection without server's identity verification is not
			 * recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL
			 * connection must be established by default if explicit option isn't set. For
			 * compliance with existing applications not using SSL the
			 * verifyServerCertificate property is set to 'false'. You need either to
			 * explicitly disable SSL by setting useSSL=false, or set useSSL=true and
			 * provide truststore for server certificate verification.
			 * 
			 * 解决方式: String url = "jdbc:mysql://localhost/test?&&useSSL=true"; url 跟上
			 * ?&&useSSL=true
			 */
			String url = "jdbc:mysql://localhost/test?&&useSSL=true";
			String user = "mephisto";
			String password = "M@ph1st0";
			connection = DriverManager.getConnection(url, user, password);

			// 3.创建statement
			statement = connection.createStatement();

			// 4.执行查询
			String sql = "Select * from demo;";
			resultSet = statement.executeQuery(sql);

			// 5.逐条查询
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				int age = resultSet.getInt("age");
				System.out.println(id + "  " + name + "  " + age);
			}
			resultSet.close();
			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				resultSet = null;
			}
			try {
				if (statement != null) {
					statement.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				statement = null;
			}
			try {
				if (connection != null) {
					connection.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				connection = null;
			}
		}
	}
}

关闭数据库类

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

public class JDBCUtil {
	public static void release(Connection con, Statement st, ResultSet rs) {
		closeCon(con);
		closeSt(st);
		closeRs(rs);
	}
	
	

	private static void closeRs(ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			resultSet = null;
		}
	}
	
	
	private static void closeSt(Statement statement) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			statement = null;
		}
	}
	
	private static void closeCon(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			connection = null;
		}
	}

}

操作流程

graph TD A[注册驱动] --> B[建立连接] B --> C[创建Statement] C --> D[执行sql,得到ResultSet] D --> E[得到结果] E --> F[释放资源]

驱动防止二次注册

// Driver() 代码中有静态代码块
// 静态代码块 --> 类加载
// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
try {
    Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

关于Class.forName

在JDBC4.0以后可以不用注册,自动在META-INF/services/java.sqlDriver注册

使用单元测试流程

定义一个类, TestXXX, 里面定义方法 testXXX.
添加JUnit jar
- 右键工程 -- add Library -- JUnit --- 选择JUnit版本

在测试方法上添加@Test

@Test
	public void testQuery(){
	....
	}

光标选中方法名字,然后右键执行单元测试,或者是打开outline视图, 然后选择方法右键点击运行

基本操作

查询

// 获取连接对象
con = JDBCUtil.getCon();

// 创建statement对象
st = con.createStatement();

// 执行sql语句
String sql = "Select * from t_user where username='"+username+"' and password = '" + password +"'";
rs = st.executeQuery(sql);
if(rs.next()) {
    System.out.println("登录成功");
}else {
    System.out.println("登录失败");
}

插入

// 获取连接对象
con = JDBCUtil.getCon();

// 根据连接对象, 得到statement
statement = con.createStatement();

// 执行添加
String sql = "Insert into demo values (4,'mephisto',19);";
// 影响的行数, 如果大于0 代表执行成功
int result = statement.executeUpdate(sql);

if (result > 0 ) {
    System.out.println("添加成功");
} else {
    System.out.println("添加失败");
}

删除

// 获取连接对象
con = JDBCUtil.getCon();

// 根据连接对象, 得到statement
statement = con.createStatement();

// 执行删除
String sql = "delete from demo where name = 'mephisto';";
// 影响的行数, 如果大于0 代表执行成功
int result = statement.executeUpdate(sql);

if (result > 0 ) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}

更新

// 获取连接对象
con = JDBCUtil.getCon();

// 根据连接对象, 得到statement
statement = con.createStatement();

// 执行更新
String sql = "update demo set name = 'lisa' where name = 'lisi';";
// 影响的行数, 如果大于0 代表执行成功
int result = statement.executeUpdate(sql);

if (result > 0 ) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}

Dao模式

Data Access Object 数据访问对象

基本流程

新建一个dao的接口,声明数据库访问的规则

package com.mephisto.dao;

public interface UserDao {

	/*

	 * 查询所有

	 */

	void findall();

}

新建一个dao的实现类, 具体实现早前的规则

package com.mephisto.dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mephisto.dao.UserDao;
import com.mephisto.util.JDBCUtil;
public class UserDaoImpl implements UserDao {

	@Override
	public void findall() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 获取连接对象
			con = JDBCUtil.getCon();

			// 创建statement对象
			st = con.createStatement();
			
			// 执行sql语句
			String sql = "Select * from t_user";
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				System.out.println(id + " " + username  +" " + password);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(con, st, rs);
		}	
	}	
}

直接实现

package com.mephisto.test;

import org.junit.jupiter.api.Test;

import com.mephisto.dao.UserDao;
import com.mephisto.dao.impl.UserDaoImpl;

public class TestUserDaoImpl {
	@Test
	public void testFindAll(){
		UserDao dao = new UserDaoImpl();
		dao.findall();
	}
}

PreparedStatement

Statement安全问题

Statement执行, 其实是拼接sql语句的, 先拼接sql语句, 然后在执行在一起

String sql = "Select * from t_user where username='"+username+"' and password = '" + password +"'";

UserDao dao = new UserDaoImpl();
dao.login("admin","adm1n' or '1=1");

select * from t_user where username = 'admin' and password = 'adm1n' or '1=1'

PreparedStatement

该对象是替换statement对象,相较statement,预先处理给定的sql语句,对其执行语句进行语法检查, 在sql语句中使用?占位符来代替后需要传递进来的变量, 后面进来的变量值, 将会被看成字符串,不会产生任何的关键字

// 获取连接对象
con = JDBCUtil.getCon();

// 创建PrepareStatement对象
String sql = "select * from t_user where username = ? and password = ?";

// 预先对sql语句执行语法校验, ? 对应的内容 传递值都为字符串
PreparedStatement ps = con.prepareStatement(sql);

// parameterIndex索引是从1开始
ps.setString(1, username);
ps.setString(2, password);
// 执行sql语句
rs = ps.executeQuery();
if(rs.next()) {
    System.out.println("登录成功");
}else {
    System.out.println("登录失败");
}

基本操作

查找

UserDaoImpl.java

Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
    // 获取连接对象
    con = JDBCUtil.getCon();

    // 创建PrepareStatement对象
    String sql = "select * from t_user where username = ? and password = ?";

    // 预先对sql语句执行语法校验, ? 对应的内容 传递值都为字符串
    PreparedStatement ps = con.prepareStatement(sql);

    // parameterIndex索引是从1开始
    ps.setString(1, username);
    ps.setString(2, password);
    // 执行sql语句
    rs = ps.executeQuery();
    if(rs.next()) {
        System.out.println("登录成功");
    }else {
        System.out.println("登录失败");
    }
} catch (SQLException e) {
    e.printStackTrace();
}finally {
    JDBCUtil.release(con, st, rs);
}

TestUserDaoImpl.java

UserDao dao = new UserDaoImpl();
// dao.login("admin","adm1n' or '1=1");
dao.login("admin","10086");

添加

UserDaoImpl.java

Connection con = null;
PreparedStatement ps = null;
try {
    con = JDBCUtil.getCon();
    String sql = "insert into t_user  values (null,?,?);";
    ps = con.prepareStatement(sql);
    ps.setString(1, username);
    ps.setString(2, password);
    int result = ps.executeUpdate();
    if(result > 0) {
        System.out.println("添加成功");
    }else {
        System.out.println("添加失败");
    }

TestUserDaoImpl.java

UserDao dao = new UserDaoImpl();
dao.insert("mephisto", "12345");

删除

UserDaoImpl.java

try {
    con = JDBCUtil.getCon();
    String sql = "Delete from t_user where id = ? or username = ? or password = ?; ";
    ps = con.prepareStatement(sql);
    ps.setInt(1, id);
    ps.setString(2, name);
    ps.setString(3, password);
    int result = ps.executeUpdate();
    if(result > 0) {
        System.out.println("删除成功");
    }else {
        System.out.println("删除失败");
    }
} catch (SQLException e) {
    e.printStackTrace();
}finally {
    JDBCUtil.release(con, ps);
}

TestUserDaoImpl.java

UserDao dao = new UserDaoImpl();
dao.delete(0, "mephisto", null);

更新

UserDaoImpl.java

Connection con = null;
PreparedStatement ps = null;

try {
    con = JDBCUtil.getCon();
    String sql = "update t_user set username= ? where id = ?";
    ps = con.prepareStatement(sql);

    ps.setString(1, name);
    ps.setInt(2, id);

    int result = ps.executeUpdate();
    if (result > 0) {
        System.out.println("更新成功");
    } else {
        System.out.println("更新失败");
    }
} catch (Exception e) {
    e.printStackTrace();
}finally {
    JDBCUtil.release(con, ps);
}

TestUserDaoImpl.java

UserDao dao = new UserDaoImpl();
dao.update(2, "张三");

posted @ 2018-09-03 21:57 无聊的子木君阅读(114) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

无聊的子木君

JDBC

JDBC

基本链接

关闭数据库类

操作流程

驱动防止二次注册

关于Class.forName

使用单元测试流程

基本操作

查询

插入

删除

更新

Dao模式

基本流程

PreparedStatement

Statement安全问题

基本操作

查找

UserDaoImpl.java

TestUserDaoImpl.java

添加

UserDaoImpl.java

TestUserDaoImpl.java

删除

UserDaoImpl.java

TestUserDaoImpl.java

更新

UserDaoImpl.java

TestUserDaoImpl.java

公告