Jdbc的核心api：
|-Driver接口： 驱动程序接口。
|-Connection connect() 用于连接数据库的方法
可以使用驱动程序管理类获取连接：
DriverManager.getConnection(url,user,pasword);
|-Connection接口： 代表和数据库的连接
|- Statement createStatement() 创建Statement接口的对象。
|- PreparedStatement prepareStatement(String sql) 创建PreparedStatement接口的对象。
|- CallableStatement prepareCall(String sql)创建CallableStatement接口的对象。
|-Statement接口：用于执行静态 SQL 语句。
|- int executeUpdate(String sql) 执行DDL和DML语句（更新sql语句）
|- ResultSet executeQuery(String sql) 执行DQL语句(查询sql语句)
|-PreparedStatement接口：用于执行预编译的 SQL 语句
|- int executeUpdate()：执行DDL和DML语句（更新sql语句）
|- ResultSet executeQuery() 执行DQL语句(查询sql语句)
|-CallableStatement接口： 用于执行存储过程的SQL语句
|- ResultSet executeQuery() 存储过程只能执行查询sql
|-ResultSet接口： 表示数据库结果集。
|- boolean next() 将光标移至下一行
|- getXXX(): 获取结果集中的每列的值
jdbc操作步骤：
1）注册驱动程序
2）获取连接对象
3）准备sql语句(DDL+DML)
4）创建Statement对象( Statement,PreparedStatment,CallableStement )
5）执行sql语句
DDL+DML: executeUpdte(sql)
DQL; executeQuery()
6）返回结果，处理结果
ResultSet
7）关闭资源
JdbcUtil：
public class JdbcUtil {
private static Properties config = new Properties();
// 1.注册驱动
static {
try {
JdbcUtil.class.getClassLoader();
config.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("database.properties"));
//config.load(JdbcUtil.class.getClassLoader().getSystemResourceAsStream("database.properties"));
//config.load(ClassLoader.getSystemResourceAsStream("database.properties"));
Class.forName(config.getProperty("driver"));
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 2.获得链接对象
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(config.getProperty("url"), config.getProperty("user"), config.getProperty("password"));
}
// 6.释放资源
public static void release(Connection connection, Statement statement){
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
resultSet = null;
}
release(connection, statement);
}
}
statement操作：
public static void main(String[] args) {
//ddl();
//insert();
// update();
Statement stmt = null;
Connection conn = null;
//模拟用户输入的数据
int id = 1;
try {
//1)获取连接
conn = JdbcUtil.getConnection();
//2)准备sql
String sql = "delete from employee where id="+id+"";
//3)创建Statement对象
stmt = conn.createStatement();
//4)执行sql
int count = stmt.executeUpdate(sql);
System.out.println("影响了"+count+"行");
} catch (SQLException e) {
e.printStackTrace();
} finally{
//5)关闭资源
JdbcUtil.close(stmt, conn);
}
}
prepareStatement操作(防注入)：
public class Demo2 {
//模拟用户输入
private static String username = "rose12121212121212121' OR 1=1 -- ";
//private static String username = "eric";
private static String password = "123456";
public static void main(String[] args) {
//statement();
/**
* 使用PreparedStatement执行.
*/
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
String sql = "SELECT * FROM user_list WHERE NAME=? AND PASSWORD=?";
stmt = conn.prepareStatement(sql);//预编译sql
//参数赋值
stmt.setString(1, username);
stmt.setString(2, password);
//发送参数
rs = stmt.executeQuery();
if(rs.next()){ // 只要返回任何一条记录就是登录成功
//登录成功
System.out.println("登录成功");
}else{
//登录失败
System.out.println("登录失败");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtil.close(rs, stmt, conn);
}
}
}
mysql数据类型与java数据类型对照表：
http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html
|MySQL Type Name| Return value ofGetColumnClassName|Returned as Java Class|
|:-:|:-:|
|BIT(1) (new in MySQL-5.0) |BIT| java.lang.Boolean|
|BIT( > 1) (new in MySQL-5.0) |BIT| byte[]|
|TINYINT |TINYINT |java.lang.Boolean if the configuration property tinyInt1isBit is set to true (the default) and the storage size is 1, or java.lang.Integer if not.|
|BOOL, BOOLEAN| TINYINT| See TINYINT, above as these are aliases for TINYINT(1), currently.|
|SMALLINT[(M)] [UNSIGNED] |SMALLINT [UNSIGNED]| java.lang.Integer (regardless of whether it is UNSIGNED or not)|
|MEDIUMINT[(M)] [UNSIGNED] |MEDIUMINT [UNSIGNED] |java.lang.Integer (regardless of whether it is UNSIGNED or not)|
|INT,INTEGER[(M)] [UNSIGNED] |INTEGER [UNSIGNED]| java.lang.Integer, if UNSIGNED java.lang.Long|
|BIGINT[(M)] [UNSIGNED] |BIGINT [UNSIGNED]| java.lang.Long, if UNSIGNED java.math.BigInteger|
|FLOAT[(M,D)] |FLOAT| java.lang.Float|
|DOUBLE[(M,B)] |DOUBLE| java.lang.Double|
|DECIMAL[(M[,D])] |DECIMAL| java.math.BigDecimal|
|DATE |DATE |java.sql.Date|
|DATETIME |DATETIME| java.sql.Timestamp|
|TIMESTAMP[(M)] |TIMESTAMP| java.sql.Timestamp|
|TIME |TIME| java.sql.Time|
|YEAR[(2｜4)] |YEAR| If yearIsDateType configuration property is set to false, then the returned object type isjava.sql.Short. If set to true (the default), then the returned object is of type java.sql.Datewith the date set to January 1st, at midnight.|
|CHAR(M) |CHAR| java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.|
|VARCHAR(M) [BINARY] |VARCHAR| java.lang.String (unless the character set for the column is BINARY, then byte[] is returned.|
|BINARY(M) |BINARY |byte[]|
|VARBINARY(M) |VARBINARY |byte[]|
|TINYBLOB |TINYBLOB| byte[]|
|TINYTEXT |VARCHAR| java.lang.String|
|BLOB |BLOB| byte[]|
|TEXT |VARCHAR| java.lang.String|
|MEDIUMBLOB |MEDIUMBLOB| byte[]|
|MEDIUMTEXT |VARCHAR| java.lang.String|
|LONGBLOB |LONGBLOB| byte[]|
|LONGTEXT |VARCHAR| java.lang.String|
|ENUM('value1','value2',...) |CHAR| java.lang.String|
|SET('value1','value2',...) |CHAR| java.lang.String|
