在开发中为了操作方便我们会使用代码的方式,而不是使用命令行。在使用JDBC操作数据库的时候我们首先要导入MySQL的jar包。
下面以登陆为例来简单介绍一下JDBC的使用方法:
package com.auicyh.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
/**
*
* @author yindj
* @date 2018年4月29号 21:30:55
*
*/
public class TestLogin {
/*定义MySQL的数据库驱动程序*/
public static final String DBDRIVER = "com.mysql.jdbc.Driver";
/*定义MySQL数据库的链接地址*/
public static final String DBURL = "jdbc:mysql://localhost:3306/aui_moto";
/*定义MySQL数据库的用户名*/
public static final String DBUSER = "root";
/*定义MySQL数据库的密码*/
public static final String DBPASS = "123456";
@Test
public void testLogin(){
try {
login("lisi","111");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void login(String username,String password) throws ClassNotFoundException, SQLException{
//1.注册驱动
Class.forName(DBDRIVER);
//2.获取连接
Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
//3.创建执行sql语句的对象
Statement stmt = conn.createStatement();
//4.书写sql语句
String sql = "select * from moto where " +"uname = '"+ username +"' and upassword = '"+ password+"'";
//5.执行sql语句
ResultSet rs = stmt.executeQuery(sql);
//6.查询结果处理
if(rs.next()){
System.out.println("恭喜你"+username+",登陆成功!");
System.out.println(sql);
}else{
System.out.println("账号或密码错误!");
}
//7.关闭流
if(rs != null) rs.close();
if(stmt != null) stmt.close();
if(conn != null) conn.close();
}
}
上面的代码会存在SQL注入的问题,一般我们使用Statement的子接口PreparedStatement来代替实现SQL语句的预加载。具体代码如下(只修改login方法)
public void loginto(String username,String password) throws ClassNotFoundException, SQLException{
//1.注册驱动
Class.forName(DBDRIVER);
//2.获取连接
Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
//3.书写sql语句
String sql = "select * from moto where uname =? and upassword =?";
//4.创建预处理对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.设置参数
pstmt.setString(1, username);//设置第一个“?”的内容
pstmt.setString(2, password);//设置第二个“?”的内容
//6.执行sql语句
ResultSet rs = pstmt.executeQuery();
//7.查询结果处理
if(rs.next()){
System.out.println("恭喜你"+username+",登陆成功!");
System.out.println(sql);
}else{
System.out.println("账号或密码错误!");
}
//7.关闭流
if(rs != null) rs.close();
if(pstmt != null) pstmt.close();
if(conn != null) conn.close();
}