JAVA对myqsl数据库操作的驱动JDBC学习
1.首先构建了数据库dat1,建立了表product,表结构如下
2.采用jdbc驱动操作来进行对数据库的链接与查询等
表的结构如下:
表的数据如下:
jdbc的主要过程:首先在工程目录下加入驱动连接的JAR包mysql-connection-java-5.1.7-bin.jar,并添加Build path.
1.注册驱动:现在的版本可以支持不写注册驱动
(1)DriverManager.registerDriver(new com.mysql.jdbc.Driver());
(2)Class.forName(com.sql.mysql.jdbc.Driver);
2.驱动连接: 要全局变量时,需声明Connection对象
Connection conn= DriverManager.getConnection(“jdbc:mysql://localhost/dat1”, “xxxx”, “xxxx”);
3. 创建statement,跟数据库互动操作
Statement st = conn.createStatement();
4.执行操作:
查询:executeQuery(sql)
String sql = “select * from product”;
ResultSet rt= st.executeQuery(sql);
插入,更新,删除等 executeUpdate(sql)
String sql = “insert into product values(null,name,age)”;
ResultSet rt= st.executeUpdate(sql);
5.遍历打印结果
while(rt.next()) {
int id = rt.getInt(“pid”);
String name = rt.getString(“pname”);
double price = rt.getDouble(“price”);
System.out.println(“id = “+id+” , name=”+name+" , price="+price);
}
下面给出代码(导入相应的包和对数据进行链接)
package heimajd;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class maintest {
public static void main(String[] args) {
// TODO Auto-generated method stub
try {
//1.注册驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName(com.sql.mysql.jdbc.Driver);
//2.链接驱动:(协议+访问数据库,mysql用户名,mysql密码)
Connection conn= DriverManager.getConnection("jdbc:mysql://localhost/dat1", "xxxx", "xxxx");
//3.创建statement,跟数据库互动操作
Statement st = conn.createStatement();
//4.执行查询
String sql = "select * from product";
ResultSet rt= st.executeQuery(sql);
//5.遍历打印结果
while(rt.next()) {
int id = rt.getInt("pid");
String name = rt.getString("pname");
double price = rt.getDouble("price");
System.out.println("id = "+id+" , name="+name+" , price="+price);
}
//关闭链接
rt.close();
st.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
```
2.利用finally来进行资源释放,局部代码块中的变量需为全局变量
```java
package utilb;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//jdbc最后释放资源调用方法
public class JDBCutil {
public static void release(Connection coon,Statement st,ResultSet rs) {
closeRs(rs);
closeSt(st);
closeConn(coon);
}
private static void closeRs(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
}catch (SQLException e) {
e.printStackTrace();
}finally {
rs = null;
}
}
private static void closeConn(Connection coon) {
try {
if( coon != null) {
coon.close();
}
}catch (SQLException e) {
e.printStackTrace();
}finally {
coon = null;
}
}
private static void closeSt(Statement st) {
try {
if(st != null) {
st.close();
}
}catch (SQLException e) {
e.printStackTrace();
}finally {
st = null;
}
}
}
3.将数据库连接和最后的资源释放写成工具类
首先是配置文件的设置,命名为jdbc.properties
内容:注册的驱动,数据库链接,mysql用户名和密码
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/dat1
name=root
password=root
文件存放在工程名根目录下时,采用 InputStream is = new FileInputStream(“jdbc.properties”);
文件存放在src根目录下时,我们采用InputStream is = JDBCutil.class.getClassLoader().getResourceAsStream(“jdbc.properties”);
package com.heima01;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCutil {
/*
static String mysqlurl ="jdbc:mysql://localhost/dat1"; //dat1是数据库的名称
static String mysqlname = "root"; //mysql的用户名和密码
static String password = "root";
static String driver ="com.mysql.jdbc.Driver"; //获取驱动注册
*/
static String driverClass =null;
static String url =null;
static String name =null;
static String password = null;
//利用配置文件来获取信息
static {
try {
Properties properties = new Properties();
//放在jdbc根目录下的读取方法
InputStream is = new FileInputStream("jdbc.properties");//获取输入流
//放在src根目录下的读取方法
//InputStream is = .......................
properties.load(is); //导入输入流
//读取配置文件属性jdbc.properties
driverClass =properties.getProperty("driverClass");
url =properties.getProperty("url");
name =properties.getProperty("name");
password = properties.getProperty("password");
} catch (Exception e) {
e.printStackTrace();
}
}
//驱动注册和链接
public static Connection getConn() {
Connection conn =null;
try {
//1.注册驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName(driverClass);
//2.链接驱动:(协议+访问数据库,mysql用户名,mysql密码)
conn= DriverManager.getConnection(url, name, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//jdbc最后释放资源调用方法
public static void release(Connection coon,Statement st,ResultSet rs) {
closeRs(rs); //释放获取的数据集
closeSt(st); //结束statement的操作
closeConn(coon);//释放驱动注册和与数据库链接
}
private static void closeRs(ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
}catch (SQLException e) {
e.printStackTrace();
}finally {
rs = null;
}
}
private static void closeConn(Connection coon) {
try {
if( coon != null) {
coon.close();
}
}catch (SQLException e) {
e.printStackTrace();
}finally {
coon = null;
}
}
private static void closeSt(Statement st) {
try {
if(st != null) {
st.close();
}
}catch (SQLException e) {
e.printStackTrace();
}finally {
st = null;
}
}
}
4.利用JUnit4进行代码测试
作用:针对某个方法中的样列进行测试和调试,避免在主方法中检测
JUnit的导入:
在elipse下的工程名下鼠标右击>Build path>add library>JUint>JUint4(然后右击选择Build path)
package com.heima02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
import com.heima01.JDBCutil;
public class test_demo {
@Test
public void testQuery(){
Connection coon = null;
Statement st = null;
ResultSet rs = null;
try {
//驱动注册和链接,调用属性文件
coon = JDBCutil.getConn();
//创建statement,跟数据库互动操作
st = coon.createStatement();
//执行查询
String sql = "select * from product";
rs= st.executeQuery(sql);
//遍历打印结果
while(rs.next()) {
int id = rs.getInt("pid");
String name = rs.getString("pname");
double price = rs.getDouble("price");
System.out.println("id = "+id+" , name="+name+" , price="+price);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭链接,调用属性文件
JDBCutil.release(coon, st, rs);
}
}
}
4.statement与preparestatement的安全问题
statement 其传入的sql语句中,若语句中有关键词时,则会默认全部为关键词,如:
st = con.createStatement();
String sql ="select * from t_user where username=’ “+username+” ’ and password=’ “+password+” ’ ";
//获取结果集
rs = st.executeQuery(sql);
SELECT * FROM t_user WHERE username=“admin” AND PASSWORD =“10086” 与
SELECT * FROM t_user WHERE username=“admin” AND PASSWORD =“10086” or 1=1;的结果一致。
preparestatement 其传入的sql语句中,预先对sql语句进行语法检查,则语句全部解析为字符串,用?占位符来替代后续传递的变量,均认为是字符串,不产生任何关键词如:
String sql ="select * from t_user where username=? and password=?";
//获取preparestatement对象
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,username);
ps.setString(2, password);
//获取结果集
rs = ps.executeQuery();