一、JDBC简介
1、JDBC是SUN公司为了简化操作数据推出一套规范。数据库厂商的驱动就是对JDBC的实现。
2、Java Data Base Connectivity（java数据库连接），它主要由接口组成。
java.sql.* javax.sql.* JDK中
数据库的驱动jar包 （mysql-connector-java-5.0.8-bin.jar ）
二、JDBC的编码步骤
0、前提：拷贝数据库的驱动到构建路径中（classpath）
1、注册驱动
2、获取与数据库的链接
3、创建代表SQL语句的对象
4、执行SQL语句
5、如果是查询语句，需要遍历结果集
6、释放占用的资源
public class JdbcDemo1 {
public static void main(String[] args) throws SQLException {
// 1、注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 2、获取与数据库的链接
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/day15", "root", ""); //没有密码则“” 空
// System.out.println(conn.getClass().getName()); 要想知道具体类型，就这么办
// 3、创建代表SQL语句的对象
Statement stmt = conn.createStatement();
// 4、执行SQL语句
ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from users");
// 5、如果是查询语句，需要遍历结果集
while (rs.next()) {
System.out.println("---------------------");
System.out.println(rs.getObject("id"));
System.out.println(rs.getObject("name"));
System.out.println(rs.getObject("password"));
System.out.println(rs.getObject("email"));
System.out.println(rs.getObject("birthday"));
}
// 6、释放占用的资源
rs.close();
stmt.close();
conn.close();
}
}三、JDBC中常用的接口详解
1、DriverManager：
作用：
a、注册驱动：
方式一：（不建议使用）
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
原因：1、依赖具体驱动。2、导致驱动注册2遍
方式二：（建议）
Class.forName("com.mysql.jdbc.Driver");
b、获取与数据库的链接
url:SUN和数据库厂商间的协议。具体查阅数据库的文档。
public static Connection getConnection(String url,String user,String password)
throws SQLException
public static Connection getConnection(String url,Properties info)
throws SQLException
public static Connection getConnection(String url)
throws SQLException
2、Connection
所有的数据库操作都是基于链接之上的。
Statement createStatement()：创建向数据库发送sql的statement对象。
//2、获取与数据库的链接
//方式一：
//Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");
//方式二：
//Properties props = new Properties();
//props.setProperty("user", "root");// 参数名：参考数据库的文档
//props.setProperty("password", "sorry");
//props.setProperty("useUnicode", "true");//编码有关的参数
//props.setProperty("characterEncoding", "utf8");
//Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15",props);
//方式三
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15?user=root&password=sorry");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15?user=root&password=");
3、Statement
Statement stmt = conn.createStatement();
作用：代表SQL语句对象。可以向数据库发送任何的SQL语句
ResultSet executeQuery(String sql):sql一般都是查询语句
int executeUpdate(String sql):sql一般是DML语句。insert update delete。返回值，操作几条记录。
boolean execute(String sql):sql可以是任意的语句。返回值不是代表成功与否。如果是查询语句，就有结果集，返回true。没有返回结果集的，返回false。
4、ResultSet
作用：封装了查询的结果集
ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from users");
List<User> users = new ArrayList<User>();
while(rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
users.add(user);
}boolean next():游标下移。返回值是有无记录
boolean previous():游标上移。
boolean absolute(int count):定位到指定的行。第一行是1。
void beforeFirst():移动游标到第一行的前面。
void afterLast():移动游标到最后一行的后面。
四、释放占用的资源
//释放资源
//JDBC编码的代码模板
public class JdbcDemo5 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");
stmt = conn.createStatement();
rs = stmt.executeQuery("select id,name,password,email,birthday from users");
List<User> users = new ArrayList<User>();
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setEmail(rs.getString("email"));
user.setBirthday(rs.getDate("birthday"));
users.add(user);
}
} catch (Exception e) {
e.printStackTrace();
} <br> finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}五、JDBC进行CRUD（增刪改查）操作
工具類：
<strong><span style="color: #ff0000;">getConnection() return conn; 獲取連接<br><br>配置文件放在 dbcgf.properties 中<br><img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAbIAAAB6CAIAAABV3UEmAAATOUlEQVR4nO2d72scxxnH9+/Rq5Y41+iSOn2pV37ltyqIwtEUmjjUaSThJo1IgpIWfGohheZUAsWuCWpDDT5KcXyUvohfCJQU9U0ujRUIiSsSx8i1LVlB0xf7a55nZp6Z29u93ZO+Xx6MvDM7P3ZmP/fMzO5OpCAIgiBNUd0FgCAIapaARQiCIKIEi5998UC3/vVNGAwGO5lGsKiUio/WRmkoQHEb/euzuzAYrArLsRjfb7Xe79AIAhlhsIosxyKYOHUCGWGwKizS77Ea73CogIBFGKwKi7IbrN47HCqm/vXNj3buwmCwEg1YnG4BizBY6ebB4tU3v2O1Cd74kCRgEQYr3fxYPHj0LbPtq8+AjA1R//rmRzvfwGCwEs2Dxb+sfvfhweH+wbf7B4fpH99+/P6Ff4eTcXA+muvtKKXUTm8uOj8IvN93enNRFIXHr1s7vbm0noX1TCrvwUzAIgxWunmw+O7rj93fP1y79MErv7v+01f//MOf//HB/uHOzYsfv39h++qP3339Mf+9XgiLIxG0ESoJi94/mIBFGKx082Dx0qun9u4f3ntwuPfg8N6Dw3v3D+89yO3Sa6f893ohLOYnNVol01tnH/MQgUUYbGLmweI7K4/fvX+4dumDX7/zz1d/P7jwm7/97I2rP1nZ+NHyn+7+7/Cdlcf99zqwOIqs+HMxUSnVv7754a1vYDBYiebB4tsvt77ee/T13qM7e4++Nuztl7/nuFsH5yNNBIt5kAaU/OBcb0c7OY6SHZjr9UxeupLVjien6KXKMg8tlZHm3JxeQUJI88R0rjRgvtScWxQiA4swWOnmweJbF57YvXtg9RZ37x68deEJ20mD8xElhIbFPCSPpUXRkkiP6Knt9OaMqM5k6XGWiysaKRVh2vmBmabGwvxP24mjuJXAIgxWr3mw2F2e/eLOwZeGxQe7y7OWc9gA2DmITv9rGzDnx5ypGenw/9Lj/ERHNL1UVHO9HXde2p/2E+Oj/okBDKJhsNrNg8U3X3zy86/s3uLnXx386sUnLedMDRbj/49QqlAsOukXO5vO4GJLLrX3IRjsmJkHi6+98NRnu/sue+2Fp2wnuYa9O705FpAPorNBbK+3o0IG0fkDMa5kGcKMQfQIpVKD89Y0XYNo48SdXm9gnGKo2AM6H966A4PBSjQPFl95/vu3/ru/dumDN/7wj5d++/fnV//aeenKrdsPP7398NPb+688/337/a0vOfSot3jetraRr0ckB4nLlYdqqVEs2pI1AeRecvGUii3jKB6FLbnYllesazpMxR7n3rp1BwaDlWgeLF44d/qTLx9+8uXD/9B/4z9+8dxp1x1elfyD6FE1fU+O6+pf39z69A4MBivRPFhcfvbp5eeeXn7u9NKzp+M/0r9PLz/79PKzE8DiTm/OsZqcRwAWYTBYaTYNHxbTxqS21Qpgsf5uBIMdJ5sGLEJuAYswWOkGLE63gEUYrHQDFqdbwCIMVroBi9Ot2jcah8GOnwGLEARBRMAiBEEQEcfifoDGyS87/fbeEQwGgzXQgEUYDAYjBizCYDAYMWARBoPBiDUKi9fmo+jU6nCUClybj6Ioml3Zqv9SauVJNL9Re3lOqJ2L1Exq54q2wnsLaiZSMwuejM6skvjv1V13+ZqsNeVOabRVicXdG93FxcXFxSvbxw6LGx2Nfp3LjgjAYs22cUywqFPeWyQ5EWAxxCrC4vaVxcXF7o3tG92KsViDXV5gnuC1+YVrPBqw2AQbD4shNjEsZrncBuCqtyqwuHujm6Bw9/hhMZB3wGIT7JhiMT4y01KbtV/hY2rVzi0GYJFMxkU6FrcunopmV7aObq7OpoH6YNk5ixfH50jinNJPp2NwKd/hSssxat6Ts9MTFyYf4/RtpfKH+mxLndEGYuzutU/GbakzkVrbSkLPbRxtrqqZwBtSPDf+mwPLoNhaKy+V6RyxoSUDhzVBv23Q4aotWVe+GRbzYpujXbEVXGZiMc4ruSbppU6usHG5XDOtga2gn05awZfv9FqtWIwZkY9AqbeYESSJMFxpRVHr4k3ej00AXZsnyR7dTka+Gc5IhJurs4QyUr6WlO1mx+K1ea38lsF4pFdwuLKgV1YO9VjccclALLtjN0gQibmlzkTqTEutbR2ttdRMS51ZCGaN71zT34lpkpGCRVhb0CLHcNGgY4Ijq1pxbzGuwio/4so3mYvMctzg11xqBdHM2hGiZahNU4svOP/psl2Ncwa7mc+rR4gzzcEXmO8UWp1YvLzAMGfDosYgzq+ksS0AurxgcS2zlCkijxLwZRkJ+RpBTgsZRG9dPGXU1zmHIIXqXiTRvHbbWKixZ+/K+Y2hUSB3T7bUmWAsCucSf8cspFjm9xZ4mSeDRTnfuEbc283iizWSzVK7DQOLGt04v9xXg7eCb1pgraVlFJjvFFqNWDQ9LwsWC87iUYhQnjp8yQzQQr7lYpHPpSbjegf75FDJpM5qu1fz+AbFMlYGYlE6l2YdZ6rfgedcQ2ObjzMZLMr5mhDRj4yDjBAs+qtpvRq2VsgKafclKegrnbqty2rGIr3Jy8MiGflSZ9CYzUyV+o9VYlGbr0xEr0Alc4vSUoC1Z29MBIvUmSJuyJ4WwTFZZi5BTAaLQr7mddZZP86CTMgguiAWxVbgDwaxKVFgcdqwGB/PR758Ck9Am5Qvm+AbrVTGWpB75T2d35TonId6BtEN9Rb3OH+dnlQ6h5WdOC1YrM5bJHmNh0Xrr2CWr/SMJLBYARaZE5dQozQsptC5uTrLQGbMabLGlvK1L3MHlcpcxRYfSCo+1WgWRqKD6ablA6VALKbYMpcpvefGN545Z2fUlxCKldlcyvBX3FpmMVNvviYWCVZCGO0oFccim9QbE4tp+purtllmoV2AxQqwSKb8bq7ORq3OfKtELNrS1FJ2OoyefGO60fWcsMe5yUJQ6vHlZdvoSOtLcqjPzNUAeSXaHJ0JaMtWYM1b13tu9myHZSDvnsvX/5ulMBIW7WXmPYFjkefbUudaTiyaqxlSK4ilIliMmdji6/LjYNGsC8nL5TACiyNhMX3rj+nKtvHcYvyQSpRAargSjkXy+p1jxi1BjxUf8vOSHhznxdYnJf2lYg9L0vrymUdebDnUa/rDZXySa8MRVL23mJ1rTrqxAjMHKiNIDAgCDuPZQ0sKhbxFku/C0e29o7WWAayIFGyEVnCXis3xWZdNnHga+2o4nw8FFot5i6b28QUdmG6yPxJs9rnFMspWfrKwxhuwCKvTynrSrRIsVv/uIKyZBizC6jPjPZDCVhYW11rO12xgJ8eARVgNxt+TG9tK8xb1+UEw8aQasAiDwWDEasMiBEFQMwUsQhAEEQGLEARBRMAiBEEQ0fRisU92meoXTGXQUTORmulIcZYidbZL4g8K5tZQLWnvMCwVvZKx1ttqpq1uhYWWmO9IEvI9lu1bouT21e+UqVZVWKTv/+VvRZfvLfY7wGI56o+Np6E6K6TgCh0/32Ky5Vt1+8pYcWqozupv4LEU5FClFP0lWB86CiZGyDISWqpkLKb1MnPUq+MpcyFV9050hkLyvYimYTFEwGKgBh3pnneGAosjasnBPlfokvbDH1eQQKTv9wzyc8XCl4jFuBaDrhOLlbqlExlEJ9uj7ipgsckaG09yZ3WGAosj6lZX8o9YqFmjJQrBcMR4Y5ZFq/V20i63jjMWNX9RP90AGj0w7Lajdneoht12OoXY7ppdoQAW+9wDNy/xkiNC1snyQYf5M0sHNQMj3PJrP1RnI7U+TPJd6icdIo5m7xzGXS2Pg1w1EhL0lJkqLqRZWX+oO1/P5KNwnWmQvVIiFrOLKfcNM2VrKyStabOBmbiPmyNdagbBrPMPApIKyVTqV45WCOzPzpjHBIsObzEAi/p6yrDbtoFxTG9xqM5a29Lxc5rMRWZN1eddIW5I/cgS42af3zN5MdpqfZg4FGc7pKOYd0t8+w20QuoR1jtaZLFGrGACQeQZnPW2NBCTQq350gtrXlX5Oi9plyJussA5TWv75nF8pZJaIT4ie4sB19nSY4VQ23/XNXdSbjVechbT16+EVvD251jHGIvJ6ks8uTgyFrVwOxctWNT8SyoLPY1OZk6gmFjkPloWX+6yWoL2WfNOnsX6kMxw8ykhufdTyTXKNY632Bdv5oBQlq+JDzIYDLvOSo7sxqJ+UL9W45fKO4gWrnPul8mLKm3+i5hUJx2R6M0Rw444s1ZK2lowtF9pJWH3kbM/pxKwqLuo5a63qAlgkUBxdCz63cCyvUU+6PDNLepH5EkfbzF0Ag6UpU8zP0UvxpJraOyrUa4x5vhGei7Hn6/tJtGv7ajXOby+0kycr1RKbIVYpcwtxo6Vq/okNOtCGRMVYRwvsGP5xVrs0H5lDfX1Z/243Cfj+pY7Nz2JTQuS8XOafhbaWCyyppWxqLdo8Ql7LxZp17SOfVxzi3KNchXGYrHncoR8radoN7P3OptzeWVhUShVLHmGtxQsKhuS7KFxmbsaEynKzXQs19bRgt5+JbeCtz+rMCyaY/nxVSUWt6+whxbVhLA41iB6VCxOyFtU2h04JL3cmhSbx6kUizKk/D8VZXuL5r00MW+RVSFkcqCYQrGY+oN6CfU6mjwyHTdXC8r9yt8KAf05CIth61QjqTIs2pioKBY59+L3Vur2FllHYdPqnscdwsjimlv0YDHNy/v4GKuUXKNcRecWCz6XI+Zr3q6k1mJRzcn7crDoLRWTDaPjzC3KKbtCrTOAWS3sEAybMZT7VUgrePtzuLdY7gpMNVhkE4qayOk60fi6c21Y5F5JWy21nVg0H461zNkHr0R7sZiNSixDOaODWgdNWQplrUSP9AydXcEr0Xoc4TqTRkm9tlKw6CmV2AquiphF8l6x0R7npmNMXkE2AjWuidCCIXeK3ArO/kwjSFiMUy7VVVQVYTHxFG1b/7HTteFup89AKGOxb91jz/ZgoyzbT032lEbcXda1xg550YpNqQyMCIW9xSx3M02WqWXRUCsw+d02nuK0puC6Fcd8LkfKt++5jMJ1Zu+6kRYU8/U+/CyXSm4Fb7GV4zqzU1xwcf7g0QsyEENZmeX2le4UuRViOfqz6zHPJFrfX98xNb2fiihJFXjgFaqk2eXSHvsa57kcqPmqugUrWC0pRScei3W9eVZIxZd0qEp8Sav4czlKqV/+AH/zv5ukshaIXCqrP5euk4jF9bbz5ZBGy3ijprAa8QGomAX4t6lMrFzl9efSdRKxSOYHp4GJ/I20sVU/FpvglzXt7xOj0vtz6TqRWIQgCHILWIQgCCICFiEIgoiARQiCICJgEYIgiAhYhCAIIqr0nehUjg+LnUxV/YgsBEFjagLeorZlAbAILEJQ4zWJQfTujW7GRWARWISghmtiWLTsE+35DK1S9IOyjt2trKGBuwZalX4UU/+Mh+VzMpHzSX1raPjebxAE1avqsaj7iqNhsd8hNBt2OzrZSGS++1XgroFWZa8G6h8fpR9Ade795tsZjqcGQVDzVB0WtY8ujrGXiwtlxrmWbzU6dg30bWlgfOxI/86HvPebZ2c4RxwIghqliW2IaplbDPIW7WS0fJK739GiBn7Z2yphSyZ5N4+wvT6ARQhquCbz3OL2lXQLg1GwqNxzi/Zvc7NBdUVYdO79Fr4zHLAIQQ1Ww7GYKsVjGujbwEXCYtAgGt4iBJ1YTQSL2qqLfrpn5z8mOtVIhsyOyOV7i76930J2hgMWIajhqvVxbnnnv37HsWaixRcZWgUW/Xu/ifvVuQ5CENQcVYJF97t/o+z8x4e78nOLNEZ1WFS+Hel8+9WpgK0BIQiqUfhUBARBEBGwCEEQRAQsQhAEEQGLEARBRMAiBEEQEbAIQRBEBCxCEAQRAYsQBEFEwCIEQRARsFipPK9uQxDUQFWOxfRrtJZNC06AgEUImj5VjMX8E93AIgRB06FKsZh8ZjH/2iKwCEFQ41UhFjMaurCYMkP7EA776A39hI7xRRx5X0BrqHODGAow8m0e43vh8p6C7u/6QBA0DaoMixoLJSzq3GE7/al+h/yHESpgX0BbqMN/075ERk+N8cc+WaZ/y5twln8FEt4iBE2fKsKiTkIPFoVPzRKxQHmLU3eo/iVGDVrZn5Yzza1XXYU2KKgf8G2WAEFQM1QJFgkUPVgkTBC3aGHMEfYFFEPzZHQsp0dtPCXHpA/c2isEbxGCpksVYHH3RleH4ihYZMwx/SuKmAJzi1oe/U7U7nTaUaevHbRSz/D5BCyajiawCEHTpfKxyHYsoLoS7i0ao1U3Yvi+gHJov5PukNDuDofddrpdgmsMPZq36MYiBtEQNB2axFsuwd6ijhxzCyvR8xphqjFGYL/bzjjY6SeoVFbskYy9W60ae9HAW4Sg6VKDsMgWj8l/U0+LBAuLNWIonXc0kratRLOBfcimrcNuOx6kA4sQNF2qHYvOiUManox39SjyvoBCKHPikpjcMXWMcX17CuanplOVwCIETZfq/FSEuO4MQRBUj4BFCIIgImARgiCICFiEIAgiwmdoIQiCiIBFCIIgImARgiCICFiEIAgi+j9aHtw6gUgHSgAAAABJRU5ErkJggg==" alt=""><br>release () 施放資源<br></span></strong>//工具类
public class JdbcUtil {
private static String driverClass;
private static String url;
private static String user;
private static String password;
static{
try {
ClassLoader cl = JdbcUtil.class.getClassLoader();
InputStream in = cl.getResourceAsStream("dbcfg.properties");
Properties props = new Properties();
props.load(in);
driverClass = props.getProperty("driverClass");
url = props.getProperty("url");
user = props.getProperty("user");
password = props.getProperty("password");
Class.forName(driverClass);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws Exception{
Connection conn = DriverManager.getConnection(url,user, password);
return conn;
}
public static void release(ResultSet rs,Statement stmt,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn!=null){
try {
conn.close();
] catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}測試：
package com.yif.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
import com.yif.util.JdbcUtil;
public class JdbcDemo6 {
@Test
public void testAdd(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.createStatement();
stmt.executeUpdate("insert into users (name,password,email,birthday) values ('范青霞','123','fqx@itcast.cn','2000-10-01')");
}catch(Exception e){
throw new RuntimeException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
@Test
public void testUpdate(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.createStatement();
stmt.executeUpdate("update users set password=111 where id=4");
}catch(Exception e){
throw new RuntimeException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
@Test
public void testDelete(){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.createStatement();
stmt.executeUpdate("delete from users where id=1");
}catch(Exception e){
throw new RuntimeException(e);
}finally{
JdbcUtil.release(rs, stmt, conn);
}
}
}sql 注入 ：
1、用户的表单输入域：防止一些特殊字符。
2、对密码加密：MD5加密
PreparedStatement接口
1、支持SQL的预编译。得到该对象时，就必须给他SQL语句。
2、支持参数占位符： ? 一个问号代表着一个参数。
注：能用PreparedStatement就不要用Statement。
