1.jdbc的概念
1:JDBC(Java Data Base Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。是Java访问数据库的标准规范。
2:JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序。
3:JDBC需要连接驱动,驱动是两个设备要进行通信,满足一定通信数据格式,数据格式由设备提供商规定,设备提供商为设备提供驱动软件,通过软件可以与该设备进行通信。
2.JDBC开发步骤
1:注册驱动。
2:获得连接。
3:获得语句执行平台。
4:执行sql语句。
5:处理结果。
6:释放资源。
3.java执行SQL的实现
public static void run1 ( ) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
String url = "jdbc:mysql://localhost:3306/my1" ;
String username = "root" ;
String password = "123456" ;
Connection connection = DriverManager. getConnection ( url, username, password) ;
Statement statement = connection. createStatement ( ) ;
int i = statement. executeUpdate ( "" +
"INSERT INTO sort(sname,sprice,sdesc) VALUES('家电来了',1000,'优惠的促销')" ) ;
System. out. println ( i) ;
statement. close ( ) ;
connection. close ( ) ;
}
4.查询的实现
public static void run3 ( ) throws Exception{
Statement statement = run2 ( ) ;
String sql = "select * from sort" ;
ResultSet resultSet = statement. executeQuery ( sql) ;
while ( resultSet. next ( ) ) {
String s = resultSet. getString ( "sid" ) ;
System. out. println ( s) ;
}
}
5.SQL攻击
SELECT * FROM users1 WHERE username='add' AND PASSWORD=222 OR 1=1;or是两边有一边为true,结果就是true,所以这是不管username和password是什么数据,都会查询到结果。
public static void run4 ( ) throws Exception {
Statement statement = run2 ( ) ;
String sql = "SELECT * FROM users1 WHERE username='add' AND PASSWORD=222 OR 1=1" ;
ResultSet resultSet = statement. executeQuery ( sql) ;
while ( resultSet. next ( ) ) {
System. out. println ( resultSet. getString ( "username" ) ) ;
}
}
public static void run5 ( ) throws Exception {
Scanner scanner = new Scanner ( System. in) ;
String username = scanner. nextLine ( ) ;
String password = scanner. nextLine ( ) ;
Statement statement = run2 ( ) ;
String sql = "SELECT * FROM users1 WHERE username='" + username+ "' AND PASSWORD='" + password+ "'" ;
System. out. println ( sql) ;
ResultSet resultSet = statement. executeQuery ( sql) ;
while ( resultSet. next ( ) ) {
System. out. println ( resultSet. getString ( "username" ) ) ;
}
}
6.防止SQL攻击,使用预编译PrepareStatement
1:SQL预编译存储,多次高效的执行。
2:防止SQL注入攻击。
3:使用Connection的 PreparedStatement prepareStatement(String sql)方法获取,PrepareStatement对象。
public static void run6 ( ) throws Exception {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
String url = "jdbc:mysql://localhost:3306/my1" ;
String username = "root" ;
String password = "123456" ;
Connection connection = DriverManager. getConnection ( url, username, password) ;
String sql = "SELECT * FROM users1 WHERE username=? AND PASSWORD=?" ;
PreparedStatement preparedStatement = connection. prepareStatement ( sql) ;
preparedStatement. setObject ( 1 , "a" ) ;
preparedStatement. setObject ( 2 , "1" ) ;
ResultSet resultSet = preparedStatement. executeQuery ( ) ;
while ( resultSet. next ( ) ) {
System. out. println ( resultSet. getString ( "id" ) ) ;
}
resultSet. close ( ) ;
preparedStatement. close ( ) ;
connection. close ( ) ;
}
7.JDBCUtils
public class JDBCUtils {
private JDBCUtils ( ) { }
private static final Connection connection;
static {
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
String url = "jdbc:mysql://localhost:3306/my1" ;
String username = "root" ;
String password = "123456" ;
connection = DriverManager. getConnection ( url, username, password) ;
} catch ( Exception se) {
throw new RuntimeException ( "数据库连接获取失败" ) ;
}
}
public static Connection getConnection ( ) {
return connection;
}
public static void close ( Connection connection, Statement statement, ResultSet resultSet) {
if ( resultSet != null) {
try {
resultSet. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( statement != null) {
try {
statement. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( connection != null) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}
8.配置文件和ClassLoader加载的JDBCUtils
public class JDBCUtils {
private JDBCUtils ( ) { }
private static Connection connection;
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
try {
readResources ( ) ;
Class. forName ( driverClass) ;
connection = DriverManager. getConnection ( url, username, password) ;
} catch ( Exception se) {
System. out. println ( se) ;
throw new RuntimeException ( "数据库连接获取失败" ) ;
}
}
private static void readResources ( ) throws IOException {
Class< Test1> test1Class = Test1. class ;
ClassLoader classLoader = test1Class. getClassLoader ( ) ;
InputStream inputStream = classLoader. getResourceAsStream ( "database.properties" ) ;
Properties properties = new Properties ( ) ;
properties. load ( inputStream) ;
driverClass = properties. getProperty ( "driverClass" ) ;
url = properties. getProperty ( "url" ) ;
username = properties. getProperty ( "username" ) ;
password = properties. getProperty ( "password" ) ;
}
public static Connection getConnection ( ) {
return connection;
}
public static void close ( Connection connection, Statement statement, ResultSet resultSet) {
if ( resultSet != null) {
try {
resultSet. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( statement != null) {
try {
statement. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
if ( connection != null) {
try {
connection. close ( ) ;
} catch ( SQLException e) {
e. printStackTrace ( ) ;
}
}
}
}
9.使用ResourceBundle加载数据库配置信息
private static String driverClassName;
private static String url;
private static String username;
private static String password;
static {
ResourceBundle resourceBundle = ResourceBundle. getBundle ( "database" ) ;
driverClassName = resourceBundle. getString ( "driverClass" ) ;
url = resourceBundle. getString ( "url" ) ;
username = resourceBundle. getString ( "username" ) ;
password = resourceBundle. getString ( "password" ) ;
}