ch10 JDBC
教学目标
- 了解什么是JDBC、熟悉JDBC的常用API
- 掌握Statement对象、PreparedStatement对象、CallableStatement对象、ResultSet对象的使用
- 掌握JDBC操作数据库的步骤
在Web开发中，不可避免的要使用数据库存储和管理数据。为了在Java语言中提供对数据库访问的支持，SUN公司于1996年提供了一套访问数据库的标准Java类库，即JDBC。
1. 什么是JDBC
JDBC全称是Java数据库连接(Java Database Connectivity)，应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、插入、更新和删除等操作。是一套用于执行SQL语句的Java API。
JDBC的优势
在程序中由JDBC和具体的数据库驱动联系，所以用户就不必直接与底层的数据库交互，这使得代码的通用性更强。
DriverManager Connection Statement ResultSet
2. JDBC的常用API
JDBC常用API
- Driver接口
- DriverManager类
- Connection接口
- Statement接口
- PreparedStatement接口
- CallableStatement接口
- ResultSet接口
接口源代码在 C:\Program Files\Java\jdk1.8.0_341\src.zip里，\java\sql。
另 java.sql.Date.java: public class Date extends java.util.Date。
2.1 Driver接口
Driver接口是所有JDBC驱动程序必须实现的接口，该接口专门提供给数据库厂商在编写驱动程序时使用。需要注意的是，在编写JDBC程序时，必须要把所使用的数据库驱动程序或类库加载到项目的classpath中（这里指MySQL驱动JAR包）。
java.sql.Driver.java
package java.sql;
public interface Driver {
Connection connect(String url, java.util.Properties info)
throws SQLException;
boolean acceptsURL(String url) throws SQLException;
DriverPropertyInfo[] getPropertyInfo(String url, java.util.Properties info)
throws SQLException;
int getMajorVersion();
int getMinorVersion();
boolean jdbcCompliant();
//------------------------- JDBC 4.1 -----------------------------------
public Logger getParentLogger() throws SQLFeatureNotSupportedException;
}
com.mysql.cj.jdbc.Driver.java
package com.mysql.cj.jdbc;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {...}
2.2 DriverManager类
源代码解读：
- 第56,49,71行 getConnection
- 第112,118行 registerDriver
package java.sql;
import java.util.Iterator;
import java.util.ServiceLoader;
import java.security.AccessController;
import java.security.PrivilegedAction;
import java.util.concurrent.CopyOnWriteArrayList;
import sun.reflect.CallerSensitive;
import sun.reflect.Reflection;
public class DriverManager {
private final static CopyOnWriteArrayList<DriverInfo> registeredDrivers = new CopyOnWriteArrayList<>();
private static volatile int loginTimeout = 0;
private static volatile java.io.PrintWriter logWriter = null;
private static volatile java.io.PrintStream logStream = null;
// Used in println() to synchronize logWriter
private final static Object logSync = new Object();
private DriverManager(){}
static {
loadInitialDrivers();
println("JDBC DriverManager initialized");
}
final static SQLPermission SET_LOG_PERMISSION =
new SQLPermission("setLog");
final static SQLPermission DEREGISTER_DRIVER_PERMISSION =
new SQLPermission("deregisterDriver");
//--------------------------JDBC 2.0-----------------------------
public static java.io.PrintWriter getLogWriter() {
return logWriter;
}
public static void setLogWriter(java.io.PrintWriter out) {
SecurityManager sec = System.getSecurityManager();
if (sec != null) {
sec.checkPermission(SET_LOG_PERMISSION);
}
logStream = null;
logWriter = out;
}
@CallerSensitive
public static Connection getConnection(String url,
java.util.Properties info) throws SQLException {
return (getConnection(url, info, Reflection.getCallerClass()));
}
@CallerSensitive
public static Connection getConnection(String url,
String user, String password) throws SQLException {
java.util.Properties info = new java.util.Properties();
if (user != null) {
info.put("user", user);
}
if (password != null) {
info.put("password", password);
}
return (getConnection(url, info, Reflection.getCallerClass()));
}
@CallerSensitive
public static Connection getConnection(String url)
throws SQLException {
java.util.Properties info = new java.util.Properties();
return (getConnection(url, info, Reflection.getCallerClass()));
}
@CallerSensitive
public static Driver getDriver(String url)
throws SQLException {
println("DriverManager.getDriver(\"" + url + "\")");
Class<?> callerClass = Reflection.getCallerClass();
// Walk through the loaded registeredDrivers attempting to locate someone
// who understands the given URL.
for (DriverInfo aDriver : registeredDrivers) {
// If the caller does not have permission to load the driver then
// skip it.
if(isDriverAllowed(aDriver.driver, callerClass)) {
try {
if(aDriver.driver.acceptsURL(url)) {
// Success!
println("getDriver returning " + aDriver.driver.getClass().getName());
return (aDriver.driver);
}
} catch(SQLException sqe) {
// Drop through and try the next driver.
}
} else {
println(" skipping: " + aDriver.driver.getClass().getName());
}
}
println("getDriver: no suitable driver");
throw new SQLException("No suitable driver", "08001");
}
public static synchronized void registerDriver(java.sql.Driver driver)
throws SQLException {
registerDriver(driver, null);
}
public static synchronized void registerDriver(java.sql.Driver driver,
DriverAction da)
throws SQLException {
/* Register the driver if it has not already been added to our list */
if(driver != null) {
registeredDrivers.addIfAbsent(new DriverInfo(driver, da));
} else {
// This is for compatibility with the original DriverManager
throw new NullPointerException();
}
println("registerDriver: " + driver);
}
@CallerSensitive
public static synchronized void deregisterDriver(Driver driver)
throws SQLException {
if (driver == null) {
return;
}
SecurityManager sec = System.getSecurityManager();
if (sec != null) {
sec.checkPermission(DEREGISTER_DRIVER_PERMISSION);
}
println("DriverManager.deregisterDriver: " + driver);
DriverInfo aDriver = new DriverInfo(driver, null);
if(registeredDrivers.contains(aDriver)) {
if (isDriverAllowed(driver, Reflection.getCallerClass())) {
DriverInfo di = registeredDrivers.get(registeredDrivers.indexOf(aDriver));
// If a DriverAction was specified, Call it to notify the
// driver that it has been deregistered
if(di.action() != null) {
di.action().deregister();
}
registeredDrivers.remove(aDriver);
} else {
// If the caller does not have permission to load the driver then
// throw a SecurityException.
throw new SecurityException();
}
} else {
println(" couldn't find driver to unload");
}
}
@CallerSensitive
public static java.util.Enumeration<Driver> getDrivers() {
java.util.Vector<Driver> result = new java.util.Vector<>();
Class<?> callerClass = Reflection.getCallerClass();
// Walk through the loaded registeredDrivers.
for(DriverInfo aDriver : registeredDrivers) {
// If the caller does not have permission to load the driver then
// skip it.
if(isDriverAllowed(aDriver.driver, callerClass)) {
result.addElement(aDriver.driver);
} else {
println(" skipping: " + aDriver.getClass().getName());
}
}
return (result.elements());
}
public static void setLoginTimeout(int seconds) {
loginTimeout = seconds;
}
public static int getLoginTimeout() {
return (loginTimeout);
}
@Deprecated
public static void setLogStream(java.io.PrintStream out) {
SecurityManager sec = System.getSecurityManager();
if (sec != null) {
sec.checkPermission(SET_LOG_PERMISSION);
}
logStream = out;
if ( out != null )
logWriter = new java.io.PrintWriter(out);
else
logWriter = null;
}
@Deprecated
public static java.io.PrintStream getLogStream() {
return logStream;
}
public static void println(String message) {
synchronized (logSync) {
if (logWriter != null) {
logWriter.println(message);
// automatic flushing is never enabled, so we must do it ourselves
logWriter.flush();
}
}
}
private static boolean isDriverAllowed(Driver driver, Class<?> caller) {
ClassLoader callerCL = caller != null ? caller.getClassLoader() : null;
return isDriverAllowed(driver, callerCL);
}
private static boolean isDriverAllowed(Driver driver, ClassLoader classLoader) {
boolean result = false;
if(driver != null) {
Class<?> aClass = null;
try {
aClass = Class.forName(driver.getClass().getName(), true, classLoader);
} catch (Exception ex) {
result = false;
}
result = ( aClass == driver.getClass() ) ? true : false;
}
return result;
}
private static void loadInitialDrivers() {
String drivers;
try {
drivers = AccessController.doPrivileged(new PrivilegedAction<String>() {
public String run() {
return System.getProperty("jdbc.drivers");
}
});
} catch (Exception ex) {
drivers = null;
}
AccessController.doPrivileged(new PrivilegedAction<Void>() {
public Void run() {
ServiceLoader<Driver> loadedDrivers = ServiceLoader.load(Driver.class);
Iterator<Driver> driversIterator = loadedDrivers.iterator();
try{
while(driversIterator.hasNext()) {
driversIterator.next();
}
} catch(Throwable t) {
// Do nothing
}
return null;
}
});
println("DriverManager.initialize: jdbc.drivers = " + drivers);
if (drivers == null || drivers.equals("")) {
return;
}
String[] driversList = drivers.split(":");
println("number of Drivers:" + driversList.length);
for (String aDriver : driversList) {
try {
println("DriverManager.Initialize: loading " + aDriver);
Class.forName(aDriver, true,
ClassLoader.getSystemClassLoader());
} catch (Exception ex) {
println("DriverManager.Initialize: load failed: " + ex);
}
}
}
// Worker method called by the public getConnection() methods.
private static Connection getConnection(
String url, java.util.Properties info, Class<?> caller) throws SQLException {
ClassLoader callerCL = caller != null ? caller.getClassLoader() : null;
synchronized(DriverManager.class) {
// synchronize loading of the correct classloader.
if (callerCL == null) {
callerCL = Thread.currentThread().getContextClassLoader();
}
}
if(url == null) {
throw new SQLException("The url cannot be null", "08001");
}
println("DriverManager.getConnection(\"" + url + "\")");
// Walk through the loaded registeredDrivers attempting to make a connection.
// Remember the first exception that gets raised so we can reraise it.
SQLException reason = null;
for(DriverInfo aDriver : registeredDrivers) {
// If the caller does not have permission to load the driver then
// skip it.
if(isDriverAllowed(aDriver.driver, callerCL)) {
try {
println(" trying " + aDriver.driver.getClass().getName());
Connection con = aDriver.driver.connect(url, info);
if (con != null) {
// Success!
println("getConnection returning " + aDriver.driver.getClass().getName());
return (con);
}
} catch (SQLException ex) {
if (reason == null) {
reason = ex;
}
}
} else {
println(" skipping: " + aDriver.getClass().getName());
}
}
// if we got here nobody could connect.
if (reason != null) {
println("getConnection failed: " + reason);
throw reason;
}
println("getConnection: no suitable driver found for "+ url);
throw new SQLException("No suitable driver found for "+ url, "08001");
}
}
class DriverInfo {
final Driver driver;
DriverAction da;
DriverInfo(Driver driver, DriverAction action) {
this.driver = driver;
da = action;
}
@Override
public boolean equals(Object other) {
return (other instanceof DriverInfo)
&& this.driver == ((DriverInfo) other).driver;
}
@Override
public int hashCode() {
return driver.hashCode();
}
@Override
public String toString() {
return ("driver[className=" + driver + "]");
}
DriverAction action() {
return da;
}
}
DriverManager类用于加载JDBC驱动并且创建与数据库的连接。在DriverManager类中，定义了两个比较重要的静态方法，如下表所示。
| 方法名称 | 功能描述 |
|---|---|
| registerDriver(Driver driver) | 该方法用于向DriverManager中注册给定的JDBC驱动程序 |
| getConnection(String url,String user,String pwd) | 该方法用于建立和数据库的连接,并返回表示连接的Connection对象 |
2.4 Connection接口
源代码解读：
- 第18行 getMetaData()
- 第5,41,58行 createStatement()
- 第6,43,60,64,66,68行 prepareStatement(String sql)
- 第7,45,62行 prepareCall(String sql)
package java.sql;
import java.util.Properties;
import java.util.concurrent.Executor;
public interface Connection extends Wrapper, AutoCloseable {
Statement createStatement() throws SQLException;
PreparedStatement prepareStatement(String sql)
throws SQLException;
CallableStatement prepareCall(String sql) throws SQLException;
void setAutoCommit(boolean autoCommit) throws SQLException;
boolean getAutoCommit() throws SQLException;
void commit() throws SQLException;
void rollback() throws SQLException;
void close() throws SQLException;
boolean isClosed() throws SQLException;
DatabaseMetaData getMetaData() throws SQLException;
String nativeSQL(String sql) throws SQLException;
void setReadOnly(boolean readOnly) throws SQLException;
boolean isReadOnly() throws SQLException;
void setCatalog(String catalog) throws SQLException;
String getCatalog() throws SQLException;
int TRANSACTION_NONE = 0;
int TRANSACTION_READ_UNCOMMITTED = 1;
int TRANSACTION_READ_COMMITTED = 2;
int TRANSACTION_REPEATABLE_READ = 4;
int TRANSACTION_SERIALIZABLE = 8;
void setTransactionIsolation(int level) throws SQLException;
int getTransactionIsolation() throws SQLException;
SQLWarning getWarnings() throws SQLException;
void clearWarnings() throws SQLException;
//--------------------------JDBC 2.0-----------------------------
Statement createStatement(int resultSetType, int resultSetConcurrency)
throws SQLException;
PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException;
CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency) throws SQLException;
java.util.Map<String,Class<?>> getTypeMap() throws SQLException;
void setTypeMap(java.util.Map<String,Class<?>> map) throws SQLException;
//--------------------------JDBC 3.0-----------------------------
void setHoldability(int holdability) throws SQLException;
int getHoldability() throws SQLException;
Savepoint setSavepoint() throws SQLException;
Savepoint setSavepoint(String name) throws SQLException;
void rollback(Savepoint savepoint) throws SQLException;
void releaseSavepoint(Savepoint savepoint) throws SQLException;
Statement createStatement(int resultSetType, int resultSetConcurrency,
int resultSetHoldability) throws SQLException;
PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability) throws SQLException;
CallableStatement prepareCall(String sql, int resultSetType,
int resultSetConcurrency, int resultSetHoldability) throws SQLException;
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
throws SQLException;
PreparedStatement prepareStatement(String sql, int columnIndexes[])
throws SQLException;
PreparedStatement prepareStatement(String sql, String columnNames[])
throws SQLException;
Clob createClob() throws SQLException;
Blob createBlob() throws SQLException;
NClob createNClob() throws SQLException;
SQLXML createSQLXML() throws SQLException;
boolean isValid(int timeout) throws SQLException;
void setClientInfo(String name, String value)
throws SQLClientInfoException;
void setClientInfo(Properties properties)
throws SQLClientInfoException;
String getClientInfo(String name)
throws SQLException;
Properties getClientInfo()
throws SQLException;
Array createArrayOf(String typeName, Object[] elements) throws
SQLException;
Struct createStruct(String typeName, Object[] attributes)
throws SQLException;
//--------------------------JDBC 4.1 -----------------------------
void setSchema(String schema) throws SQLException;
String getSchema() throws SQLException;
void abort(Executor executor) throws SQLException;
void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException;
int getNetworkTimeout() throws SQLException;
}
| 方法名称 | 功能描述 |
|---|---|
| getMetaData() | 该方法用于返回表示数据库的元数据的DatabaseMetaData对象 |
| createStatement() | 用于创建一个Statement对象来将非参数化SQL语句发送到数据库 |
| prepareStatement(String sql) | 用于创建一个PreparedStatement对象来将参数化的SQL语句发送到数据库 |
| prepareCall(String sql) | 用于创建一个CallableStatement对象来调用参数化的数据库存储过程 |
参数化 == prepare == ? == 变量
2.5 Statement接口
解读源代码：
- executeQuery 第3行
- executeUpdate 第4，59-61行
- close() 第5行
- execute 第25，63-65行
package java.sql;
public interface Statement extends Wrapper, AutoCloseable {
ResultSet executeQuery(String sql) throws SQLException;
int executeUpdate(String sql) throws SQLException;
void close() throws SQLException;
//----------------------------------------------------------------------
int getMaxFieldSize() throws SQLException;
void setMaxFieldSize(int max) throws SQLException;
int getMaxRows() throws SQLException;
void setMaxRows(int max) throws SQLException;
void setEscapeProcessing(boolean enable) throws SQLException;
int getQueryTimeout() throws SQLException;
void setQueryTimeout(int seconds) throws SQLException;
SQLWarning getWarnings() throws SQLException;
void clearWarnings() throws SQLException;
void setCursorName(String name) throws SQLException;
//----------------------- Multiple Results --------------------------
boolean execute(String sql) throws SQLException;
ResultSet getResultSet() throws SQLException;
int getUpdateCount() throws SQLException;
boolean getMoreResults() throws SQLException;
//--------------------------JDBC 2.0-----------------------------
void setFetchDirection(int direction) throws SQLException;
int getFetchDirection() throws SQLException;
void setFetchSize(int rows) throws SQLException;
int getFetchSize() throws SQLException;
int getResultSetConcurrency() throws SQLException;
int getResultSetType() throws SQLException;
void addBatch( String sql ) throws SQLException;
void clearBatch() throws SQLException;
int[] executeBatch() throws SQLException;
Connection getConnection() throws SQLException;
//--------------------------JDBC 3.0-----------------------------
int CLOSE_CURRENT_RESULT = 1;
int KEEP_CURRENT_RESULT = 2;
int CLOSE_ALL_RESULTS = 3;
int SUCCESS_NO_INFO = -2;
int EXECUTE_FAILED = -3;
int RETURN_GENERATED_KEYS = 1;
int NO_GENERATED_KEYS = 2;
boolean getMoreResults(int current) throws SQLException;
ResultSet getGeneratedKeys() throws SQLException;
int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException;
int executeUpdate(String sql, int columnIndexes[]) throws SQLException;
int executeUpdate(String sql, String columnNames[]) throws SQLException;
boolean execute(String sql, int autoGeneratedKeys) throws SQLException;
boolean execute(String sql, int columnIndexes[]) throws SQLException;
boolean execute(String sql, String columnNames[]) throws SQLException;
int getResultSetHoldability() throws SQLException;
boolean isClosed() throws SQLException;
void setPoolable(boolean poolable)
throws SQLException;
boolean isPoolable()
throws SQLException;
//--------------------------JDBC 4.1 -----------------------------
public void closeOnCompletion() throws SQLException;
public boolean isCloseOnCompletion() throws SQLException;
//--------------------------JDBC 4.2 -----------------------------
default long getLargeUpdateCount() throws SQLException {
throw new UnsupportedOperationException("getLargeUpdateCount not implemented");
}
default void setLargeMaxRows(long max) throws SQLException {
throw new UnsupportedOperationException("setLargeMaxRows not implemented");
}
default long getLargeMaxRows() throws SQLException {
return 0;
}
default long[] executeLargeBatch() throws SQLException {
throw new UnsupportedOperationException("executeLargeBatch not implemented");
}
default long executeLargeUpdate(String sql) throws SQLException {
throw new UnsupportedOperationException("executeLargeUpdate not implemented");
}
default long executeLargeUpdate(String sql, int autoGeneratedKeys)
throws SQLException {
throw new SQLFeatureNotSupportedException("executeLargeUpdate not implemented");
}
default long executeLargeUpdate(String sql, int columnIndexes[]) throws SQLException {
throw new SQLFeatureNotSupportedException("executeLargeUpdate not implemented");
}
default long executeLargeUpdate(String sql, String columnNames[])
throws SQLException {
throw new SQLFeatureNotSupportedException("executeLargeUpdate not implemented");
}
}
| 方法名称 | 功能描述 |
|---|---|
| execute(String sql) | 用于执行各种SQL 语句,该方法返回一个boolean 类型的值,如果为true,表示所执行的sQL 语句具备查询结果,可通过Statement的getResultSet()方法获得查询结果 |
| executeUpdate(String sql) | 用于执行 SQL中的insert、update和delete语句。该方法返回一个int类型的值，表示数据库中受该SQL语句影响的记录的行数 |
| executeQuery(String sql) | 用于执行SQL 中的select 语句，该方法返回一个表示查询结果的ResultSet对象 |
2.6 PreparedStatement接口
解读源代码：
- executeQuery() 第10行
- executeUpdate() 第11行
- clearParameters() 第35行
- addBatch() 第44行
- setXxx
package java.sql;
import java.math.BigDecimal;
import java.util.Calendar;
import java.io.Reader;
import java.io.InputStream;
public interface PreparedStatement extends Statement {
ResultSet executeQuery() throws SQLException;
int executeUpdate() throws SQLException;
void setNull(int parameterIndex, int sqlType) throws SQLException;
void setBoolean(int parameterIndex, boolean x) throws SQLException;
void setByte(int parameterIndex, byte x) throws SQLException;
void setShort(int parameterIndex, short x) throws SQLException;
void setInt(int parameterIndex, int x) throws SQLException;
void setLong(int parameterIndex, long x) throws SQLException;
void setFloat(int parameterIndex, float x) throws SQLException;
void setDouble(int parameterIndex, double x) throws SQLException;
void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException;
void setString(int parameterIndex, String x) throws SQLException;
void setBytes(int parameterIndex, byte x[]) throws SQLException;
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
void setTime(int parameterIndex, java.sql.Time x) throws SQLException;
void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;
void setAsciiStream(int parameterIndex, java.io.InputStream x, int length)
throws SQLException;
@Deprecated
void setUnicodeStream(int parameterIndex, java.io.InputStream x,
int length) throws SQLException;
void setBinaryStream(int parameterIndex, java.io.InputStream x,
int length) throws SQLException;
void clearParameters() throws SQLException;
// Advanced features:
void setObject(int parameterIndex, Object x, int targetSqlType)
throws SQLException;
void setObject(int parameterIndex, Object x) throws SQLException;
boolean execute() throws SQLException;
//--------------------------JDBC 2.0-----------------------------
void addBatch() throws SQLException;
void setCharacterStream(int parameterIndex,
java.io.Reader reader,
int length) throws SQLException;
void setRef (int parameterIndex, Ref x) throws SQLException;
void setBlob (int parameterIndex, Blob x) throws SQLException;
void setClob (int parameterIndex, Clob x) throws SQLException;
void setArray (int parameterIndex, Array x) throws SQLException;
ResultSetMetaData getMetaData() throws SQLException;
void setDate(int parameterIndex, java.sql.Date x, Calendar cal)
throws SQLException;
void setTime(int parameterIndex, java.sql.Time x, Calendar cal)
throws SQLException;
void setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal)
throws SQLException;
void setNull (int parameterIndex, int sqlType, String typeName)
throws SQLException;
//------------------------- JDBC 3.0 -----------------------------------
void setURL(int parameterIndex, java.net.URL x) throws SQLException;
ParameterMetaData getParameterMetaData() throws SQLException;
//------------------------- JDBC 4.0 -----------------------------------
void setRowId(int parameterIndex, RowId x) throws SQLException;
void setNString(int parameterIndex, String value) throws SQLException;
void setNCharacterStream(int parameterIndex, Reader value, long length)
throws SQLException;
void setNClob(int parameterIndex, NClob value) throws SQLException;
void setClob(int parameterIndex, Reader reader, long length)
throws SQLException;
void setBlob(int parameterIndex, InputStream inputStream, long length)
throws SQLException;
void setNClob(int parameterIndex, Reader reader, long length)
throws SQLException;
void setSQLXML(int parameterIndex, SQLXML xmlObject) throws SQLException;
void setObject(int parameterIndex, Object x, int targetSqlType, int scaleOrLength)
throws SQLException;
void setAsciiStream(int parameterIndex, java.io.InputStream x, long length)
throws SQLException;
void setBinaryStream(int parameterIndex, java.io.InputStream x,
long length) throws SQLException;
void setCharacterStream(int parameterIndex, java.io.Reader reader,
long length) throws SQLException;
void setAsciiStream(int parameterIndex, java.io.InputStream x)
throws SQLException;
void setBinaryStream(int parameterIndex, java.io.InputStream x)
throws SQLException;
void setCharacterStream(int parameterIndex,
java.io.Reader reader) throws SQLException;
void setNCharacterStream(int parameterIndex, Reader value) throws SQLException;
void setClob(int parameterIndex, Reader reader) throws SQLException;
void setBlob(int parameterIndex, InputStream inputStream) throws SQLException;
void setNClob(int parameterIndex, Reader reader) throws SQLException;
//------------------------- JDBC 4.2 -----------------------------------
default void setObject(int parameterIndex, Object x, SQLType targetSqlType,
int scaleOrLength) throws SQLException {
throw new SQLFeatureNotSupportedException("setObject not implemented");
}
default void setObject(int parameterIndex, Object x, SQLType targetSqlType)
throws SQLException {
throw new SQLFeatureNotSupportedException("setObject not implemented");
}
default long executeLargeUpdate() throws SQLException {
throw new UnsupportedOperationException("executeLargeUpdate not implemented");
}
}
| 方法名称 | 功能描述 |
|---|---|
| executeUpdate() | 在此 PreparedStatement 对象中执行SQL 语句,该语句必须是一个DML 语句或者是无返回内容的SQL语句,比如 DDL 语句 |
| executeQuery() | 在此PreparedStatement 对象中执行SQL查询,该方法返回的是ResultSet对象select |
| setInt(int parameterIndex,int x) | 将指定参数设置为给定的int值 |
| setFloat(int parameterIndex,float x) | 将指定参数设置为给定的float值 |
| setString(int parameterIndex,String x) | 将指定参数设置为给定的String值 |
| setDate(int parameterIndex,Date x) | 将指定参数设置为给定的Date值 |
| setCharacterStream(parameterIndex,reader,length) | 将指定的输入流写入数据库的文本字段 |
| setBinaryStream(parameterIndex,length) | 将二进制的输入流数据写入到二进制字段中 |
| addBatch() | 将一组参数添加到此PreparedStatement对象的批处理命令中 |
2.7 CallableStatement接口
解读源代码：
- registerOutParameter(...) 第9,11,50,52,54,174,178,182,186,190.194行
- getXxx, setXxx
package java.sql;
import java.math.BigDecimal;
import java.util.Calendar;
import java.io.Reader;
import java.io.InputStream;
public interface CallableStatement extends PreparedStatement {
void registerOutParameter(int parameterIndex, int sqlType)
throws SQLException;
void registerOutParameter(int parameterIndex, int sqlType, int scale)
throws SQLException;
boolean wasNull() throws SQLException;
String getString(int parameterIndex) throws SQLException;
boolean getBoolean(int parameterIndex) throws SQLException;
byte getByte(int parameterIndex) throws SQLException;
short getShort(int parameterIndex) throws SQLException;
int getInt(int parameterIndex) throws SQLException;
long getLong(int parameterIndex) throws SQLException;
float getFloat(int parameterIndex) throws SQLException;
double getDouble(int parameterIndex) throws SQLException;
@Deprecated
BigDecimal getBigDecimal(int parameterIndex, int scale)
throws SQLException;
byte[] getBytes(int parameterIndex) throws SQLException;
java.sql.Date getDate(int parameterIndex) throws SQLException;
java.sql.Time getTime(int parameterIndex) throws SQLException;
java.sql.Timestamp getTimestamp(int parameterIndex)
throws SQLException;
Object getObject(int parameterIndex) throws SQLException;
//--------------------------JDBC 2.0-----------------------------
BigDecimal getBigDecimal(int parameterIndex) throws SQLException;
Object getObject(int parameterIndex, java.util.Map<String,Class<?>> map)
throws SQLException;
Ref getRef (int parameterIndex) throws SQLException;
Blob getBlob (int parameterIndex) throws SQLException;
Clob getClob (int parameterIndex) throws SQLException;
Array getArray (int parameterIndex) throws SQLException;
java.sql.Date getDate(int parameterIndex, Calendar cal)
throws SQLException;
java.sql.Time getTime(int parameterIndex, Calendar cal)
throws SQLException;
java.sql.Timestamp getTimestamp(int parameterIndex, Calendar cal)
throws SQLException;
void registerOutParameter (int parameterIndex, int sqlType, String typeName)
throws SQLException;
//--------------------------JDBC 3.0-----------------------------
void registerOutParameter(String parameterName, int sqlType)
throws SQLException;
void registerOutParameter(String parameterName, int sqlType, int scale)
throws SQLException;
void registerOutParameter (String parameterName, int sqlType, String typeName)
throws SQLException;
java.net.URL getURL(int parameterIndex) throws SQLException;
void setURL(String parameterName, java.net.URL val) throws SQLException;
void setNull(String parameterName, int sqlType) throws SQLException;
void setBoolean(String parameterName, boolean x) throws SQLException;
void setByte(String parameterName, byte x) throws SQLException;
void setShort(String parameterName, short x) throws SQLException;
void setInt(String parameterName, int x) throws SQLException;
void setLong(String parameterName, long x) throws SQLException;
void setFloat(String parameterName, float x) throws SQLException;
void setDouble(String parameterName, double x) throws SQLException;
void setBigDecimal(String parameterName, BigDecimal x) throws SQLException;
void setString(String parameterName, String x) throws SQLException;
void setBytes(String parameterName, byte x[]) throws SQLException;
void setDate(String parameterName, java.sql.Date x) throws SQLException;
void setTime(String parameterName, java.sql.Time x) throws SQLException;
void setTimestamp(String parameterName, java.sql.Timestamp x)
throws SQLException;
void setAsciiStream(String parameterName, java.io.InputStream x, int length)
throws SQLException;
void setBinaryStream(String parameterName, java.io.InputStream x,
int length) throws SQLException;
void setObject(String parameterName, Object x, int targetSqlType, int scale)
throws SQLException;
void setObject(String parameterName, Object x, int targetSqlType)
throws SQLException;
void setObject(String parameterName, Object x) throws SQLException;
void setCharacterStream(String parameterName, java.io.Reader reader,
int length) throws SQLException;
void setDate(String parameterName, java.sql.Date x, Calendar cal)
throws SQLException;
void setTime(String parameterName, java.sql.Time x, Calendar cal)
throws SQLException;
void setTimestamp(String parameterName, java.sql.Timestamp x, Calendar cal)
throws SQLException;
void setNull (String parameterName, int sqlType, String typeName)
throws SQLException;
String getString(String parameterName) throws SQLException;
boolean getBoolean(String parameterName) throws SQLException;
byte getByte(String parameterName) throws SQLException;
short getShort(String parameterName) throws SQLException;
int getInt(String parameterName) throws SQLException;
long getLong(String parameterName) throws SQLException;
float getFloat(String parameterName) throws SQLException;
double getDouble(String parameterName) throws SQLException;
byte[] getBytes(String parameterName) throws SQLException;
java.sql.Date getDate(String parameterName) throws SQLException;
java.sql.Time getTime(String parameterName) throws SQLException;
java.sql.Timestamp getTimestamp(String parameterName) throws SQLException;
Object getObject(String parameterName) throws SQLException;
BigDecimal getBigDecimal(String parameterName) throws SQLException;
Object getObject(String parameterName, java.util.Map<String,Class<?>> map)
throws SQLException;
Ref getRef (String parameterName) throws SQLException;
Blob getBlob (String parameterName) throws SQLException;
Clob getClob (String parameterName) throws SQLException;
Array getArray (String parameterName) throws SQLException;
java.sql.Date getDate(String parameterName, Calendar cal) throws SQLException;
java.sql.Time getTime(String parameterName, Calendar cal) throws SQLException;
java.sql.Timestamp getTimestamp(String parameterName, Calendar cal)
throws SQLException;
java.net.URL getURL(String parameterName) throws SQLException;
//------------------------- JDBC 4.0 -----------------------------------
RowId getRowId(int parameterIndex) throws SQLException;
RowId getRowId(String parameterName) throws SQLException;
void setRowId(String parameterName, RowId x) throws SQLException;
void setNString(String parameterName, String value)
throws SQLException;
void setNCharacterStream(String parameterName, Reader value, long length)
throws SQLException;
void setNClob(String parameterName, NClob value) throws SQLException;
void setClob(String parameterName, Reader reader, long length)
throws SQLException;
void setBlob(String parameterName, InputStream inputStream, long length)
throws SQLException;
void setNClob(String parameterName, Reader reader, long length)
throws SQLException;
NClob getNClob (int parameterIndex) throws SQLException;
NClob getNClob (String parameterName) throws SQLException;
void setSQLXML(String parameterName, SQLXML xmlObject) throws SQLException;
SQLXML getSQLXML(int parameterIndex) throws SQLException;
SQLXML getSQLXML(String parameterName) throws SQLException;
String getNString(int parameterIndex) throws SQLException;
String getNString(String parameterName) throws SQLException;
java.io.Reader getNCharacterStream(int parameterIndex) throws SQLException;
java.io.Reader getNCharacterStream(String parameterName) throws SQLException;
java.io.Reader getCharacterStream(int parameterIndex) throws SQLException;
java.io.Reader getCharacterStream(String parameterName) throws SQLException;
void setBlob (String parameterName, Blob x) throws SQLException;
void setClob (String parameterName, Clob x) throws SQLException;
void setAsciiStream(String parameterName, java.io.InputStream x, long length)
throws SQLException;
void setBinaryStream(String parameterName, java.io.InputStream x,
long length) throws SQLException;
void setCharacterStream(String parameterName, java.io.Reader reader,
long length) throws SQLException;
void setAsciiStream(String parameterName, java.io.InputStream x) throws SQLException;
void setBinaryStream(String parameterName, java.io.InputStream x) throws SQLException;
void setCharacterStream(String parameterName, java.io.Reader reader) throws SQLException;
void setNCharacterStream(String parameterName, Reader value) throws SQLException;
void setClob(String parameterName, Reader reader) throws SQLException;
void setBlob(String parameterName, InputStream inputStream) throws SQLException;
void setNClob(String parameterName, Reader reader) throws SQLException;
//------------------------- JDBC 4.1 -----------------------------------
public <T> T getObject(int parameterIndex, Class<T> type) throws SQLException;
public <T> T getObject(String parameterName, Class<T> type) throws SQLException;
//------------------------- JDBC 4.2 -----------------------------------
default void setObject(String parameterName, Object x, SQLType targetSqlType,
int scaleOrLength) throws SQLException {
throw new SQLFeatureNotSupportedException("setObject not implemented");
}
default void setObject(String parameterName, Object x, SQLType targetSqlType)
throws SQLException {
throw new SQLFeatureNotSupportedException("setObject not implemented");
}
default void registerOutParameter(int parameterIndex, SQLType sqlType)
throws SQLException {
throw new SQLFeatureNotSupportedException("registerOutParameter not implemented");
}
default void registerOutParameter(int parameterIndex, SQLType sqlType,
int scale) throws SQLException {
throw new SQLFeatureNotSupportedException("registerOutParameter not implemented");
}
default void registerOutParameter (int parameterIndex, SQLType sqlType,
String typeName) throws SQLException {
throw new SQLFeatureNotSupportedException("registerOutParameter not implemented");
}
default void registerOutParameter(String parameterName, SQLType sqlType)
throws SQLException {
throw new SQLFeatureNotSupportedException("registerOutParameter not implemented");
}
default void registerOutParameter(String parameterName, SQLType sqlType,
int scale) throws SQLException {
throw new SQLFeatureNotSupportedException("registerOutParameter not implemented");
}
default void registerOutParameter (String parameterName, SQLType sqlType,
String typeName) throws SQLException {
throw new SQLFeatureNotSupportedException("registerOutParameter not implemented");
}
}
| 方法名称 | 功能描述 |
|---|---|
| registerOutParameter(int parameterIndex, int sqlType) | 按顺序位置将OUT参数注册为SQL类型。其中,parameterIndex表示顺序位置,sqlType表示SQL类型 |
| setNull(String parameterName, int sqlType) | 将指定参数设置为SQL类型的NULL |
| setString(String parameterName,String x) | 将指定参数设置为给定的Java类型的String值 |
| wasNull() | 查询最后一个读取的OUT参数是否为SQL类型的NULL值 |
| getInt(int parameterIndex) | 以Java语言中int值的形式获取指定的数据库中 |
2.8 ResultSet接口
解读源代码：
- next() 第10行
- isBeforeFirst(),isAfterLast(),isFirst(),isLast(),beforeFirst(),afterLast(),first(),last(),int getRow(),absolute( int row ),relative( int rows ),previous() 第74-85行
- close() 第11行
- getXxx, updateXxx
package java.sql;
import java.math.BigDecimal;
import java.util.Calendar;
import java.io.Reader;
import java.io.InputStream;
public interface ResultSet extends Wrapper, AutoCloseable {
boolean next() throws SQLException;
void close() throws SQLException;
boolean wasNull() throws SQLException;
String getString(int columnIndex) throws SQLException;
boolean getBoolean(int columnIndex) throws SQLException;
byte getByte(int columnIndex) throws SQLException;
short getShort(int columnIndex) throws SQLException;
int getInt(int columnIndex) throws SQLException;
long getLong(int columnIndex) throws SQLException;
float getFloat(int columnIndex) throws SQLException;
double getDouble(int columnIndex) throws SQLException;
@Deprecated
BigDecimal getBigDecimal(int columnIndex, int scale) throws SQLException;
byte[] getBytes(int columnIndex) throws SQLException;
java.sql.Date getDate(int columnIndex) throws SQLException;
java.sql.Time getTime(int columnIndex) throws SQLException;
java.sql.Timestamp getTimestamp(int columnIndex) throws SQLException;
java.io.InputStream getAsciiStream(int columnIndex) throws SQLException;
@Deprecated
java.io.InputStream getUnicodeStream(int columnIndex) throws SQLException;
java.io.InputStream getBinaryStream(int columnIndex) throws SQLException;
String getString(String columnLabel) throws SQLException;
boolean getBoolean(String columnLabel) throws SQLException;
byte getByte(String columnLabel) throws SQLException;
short getShort(String columnLabel) throws SQLException;
int getInt(String columnLabel) throws SQLException;
long getLong(String columnLabel) throws SQLException;
float getFloat(String columnLabel) throws SQLException;
double getDouble(String columnLabel) throws SQLException;
@Deprecated
BigDecimal getBigDecimal(String columnLabel, int scale) throws SQLException;
byte[] getBytes(String columnLabel) throws SQLException;
java.sql.Date getDate(String columnLabel) throws SQLException;
java.sql.Time getTime(String columnLabel) throws SQLException;
java.sql.Timestamp getTimestamp(String columnLabel) throws SQLException;
java.io.InputStream getAsciiStream(String columnLabel) throws SQLException;
@Deprecated
java.io.InputStream getUnicodeStream(String columnLabel) throws SQLException;
java.io.InputStream getBinaryStream(String columnLabel) throws SQLException;
SQLWarning getWarnings() throws SQLException;
String getCursorName() throws SQLException;
ResultSetMetaData getMetaData() throws SQLException;
Object getObject(int columnIndex) throws SQLException;
Object getObject(String columnLabel) throws SQLException;
void clearWarnings() throws SQLException;
int findColumn(String columnLabel) throws SQLException;
//--------------------------JDBC 2.0-----------------------------------
//---------------------------------------------------------------------
// Getters and Setters
//---------------------------------------------------------------------
java.io.Reader getCharacterStream(int columnIndex) throws SQLException;
java.io.Reader getCharacterStream(String columnLabel) throws SQLException;
BigDecimal getBigDecimal(int columnIndex) throws SQLException;
BigDecimal getBigDecimal(String columnLabel) throws SQLException;
//---------------------------------------------------------------------
// Traversal/Positioning
//---------------------------------------------------------------------
boolean isBeforeFirst() throws SQLException;
boolean isAfterLast() throws SQLException;
boolean isFirst() throws SQLException;
boolean isLast() throws SQLException;
void beforeFirst() throws SQLException;
void afterLast() throws SQLException;
boolean first() throws SQLException;
boolean last() throws SQLException;
int getRow() throws SQLException;
boolean absolute( int row ) throws SQLException;
boolean relative( int rows ) throws SQLException;
boolean previous() throws SQLException;
//---------------------------------------------------------------------
// Properties
//---------------------------------------------------------------------
int FETCH_FORWARD = 1000;
int FETCH_REVERSE = 1001;
int FETCH_UNKNOWN = 1002;
void setFetchDirection(int direction) throws SQLException;
int getFetchDirection() throws SQLException;
void setFetchSize(int rows) throws SQLException;
int getFetchSize() throws SQLException;
int TYPE_FORWARD_ONLY = 1003;
int TYPE_SCROLL_INSENSITIVE = 1004;
int TYPE_SCROLL_SENSITIVE = 1005;
int getType() throws SQLException;
int CONCUR_READ_ONLY = 1007;
int CONCUR_UPDATABLE = 1008;
int getConcurrency() throws SQLException;
boolean rowUpdated() throws SQLException;
boolean rowInserted() throws SQLException;
boolean rowDeleted() throws SQLException;
void updateNull(int columnIndex) throws SQLException;
void updateBoolean(int columnIndex, boolean x) throws SQLException;
void updateByte(int columnIndex, byte x) throws SQLException;
void updateShort(int columnIndex, short x) throws SQLException;
void updateInt(int columnIndex, int x) throws SQLException;
void updateLong(int columnIndex, long x) throws SQLException;
void updateFloat(int columnIndex, float x) throws SQLException;
void updateDouble(int columnIndex, double x) throws SQLException;
void updateBigDecimal(int columnIndex, BigDecimal x) throws SQLException;
void updateString(int columnIndex, String x) throws SQLException;
void updateBytes(int columnIndex, byte x[]) throws SQLException;
void updateDate(int columnIndex, java.sql.Date x) throws SQLException;
void updateTime(int columnIndex, java.sql.Time x) throws SQLException;
void updateTimestamp(int columnIndex, java.sql.Timestamp x)
throws SQLException;
void updateAsciiStream(int columnIndex, java.io.InputStream x,
int length) throws SQLException;
void updateBinaryStream(int columnIndex, java.io.InputStream x,
int length) throws SQLException;
void updateCharacterStream(int columnIndex, java.io.Reader x,
int length) throws SQLException;
void updateObject(int columnIndex, Object x, int scaleOrLength)
throws SQLException;
void updateObject(int columnIndex, Object x) throws SQLException;
void updateNull(String columnLabel) throws SQLException;
void updateBoolean(String columnLabel, boolean x) throws SQLException;
void updateByte(String columnLabel, byte x) throws SQLException;
void updateShort(String columnLabel, short x) throws SQLException;
void updateInt(String columnLabel, int x) throws SQLException;
void updateLong(String columnLabel, long x) throws SQLException;
void updateFloat(String columnLabel, float x) throws SQLException;
void updateDouble(String columnLabel, double x) throws SQLException;
void updateBigDecimal(String columnLabel, BigDecimal x) throws SQLException;
void updateString(String columnLabel, String x) throws SQLException;
void updateBytes(String columnLabel, byte x[]) throws SQLException;
void updateDate(String columnLabel, java.sql.Date x) throws SQLException;
void updateTime(String columnLabel, java.sql.Time x) throws SQLException;
void updateTimestamp(String columnLabel, java.sql.Timestamp x)
throws SQLException;
void updateAsciiStream(String columnLabel, java.io.InputStream x,
int length) throws SQLException;
void updateBinaryStream(String columnLabel, java.io.InputStream x,
int length) throws SQLException;
void updateCharacterStream(String columnLabel, java.io.Reader reader,
int length) throws SQLException;
void updateObject(String columnLabel, Object x, int scaleOrLength)
throws SQLException;
void updateObject(String columnLabel, Object x) throws SQLException;
void insertRow() throws SQLException;
void updateRow() throws SQLException;
void deleteRow() throws SQLException;
void refreshRow() throws SQLException;
void cancelRowUpdates() throws SQLException;
void moveToInsertRow() throws SQLException;
void moveToCurrentRow() throws SQLException;
Statement getStatement() throws SQLException;
Object getObject(int columnIndex, java.util.Map<String,Class<?>> map)
throws SQLException;
Ref getRef(int columnIndex) throws SQLException;
Blob getBlob(int columnIndex) throws SQLException;
Clob getClob(int columnIndex) throws SQLException;
Array getArray(int columnIndex) throws SQLException;
Object getObject(String columnLabel, java.util.Map<String,Class<?>> map)
throws SQLException;
Ref getRef(String columnLabel) throws SQLException;
Blob getBlob(String columnLabel) throws SQLException;
Clob getClob(String columnLabel) throws SQLException;
Array getArray(String columnLabel) throws SQLException;
java.sql.Date getDate(int columnIndex, Calendar cal) throws SQLException;
java.sql.Date getDate(String columnLabel, Calendar cal) throws SQLException;
java.sql.Time getTime(int columnIndex, Calendar cal) throws SQLException;
java.sql.Time getTime(String columnLabel, Calendar cal) throws SQLException;
java.sql.Timestamp getTimestamp(int columnIndex, Calendar cal)
throws SQLException;
java.sql.Timestamp getTimestamp(String columnLabel, Calendar cal)
throws SQLException;
//-------------------------- JDBC 3.0 ----------------------------------------
int HOLD_CURSORS_OVER_COMMIT = 1;
int CLOSE_CURSORS_AT_COMMIT = 2;
java.net.URL getURL(int columnIndex) throws SQLException;
java.net.URL getURL(String columnLabel) throws SQLException;
void updateRef(int columnIndex, java.sql.Ref x) throws SQLException;
void updateRef(String columnLabel, java.sql.Ref x) throws SQLException;
void updateBlob(int columnIndex, java.sql.Blob x) throws SQLException;
void updateBlob(String columnLabel, java.sql.Blob x) throws SQLException;
void updateClob(int columnIndex, java.sql.Clob x) throws SQLException;
void updateClob(String columnLabel, java.sql.Clob x) throws SQLException;
void updateArray(int columnIndex, java.sql.Array x) throws SQLException;
void updateArray(String columnLabel, java.sql.Array x) throws SQLException;
//------------------------- JDBC 4.0 -----------------------------------
RowId getRowId(int columnIndex) throws SQLException;
RowId getRowId(String columnLabel) throws SQLException;
void updateRowId(int columnIndex, RowId x) throws SQLException;
void updateRowId(String columnLabel, RowId x) throws SQLException;
int getHoldability() throws SQLException;
boolean isClosed() throws SQLException;
void updateNString(int columnIndex, String nString) throws SQLException;
void updateNString(String columnLabel, String nString) throws SQLException;
void updateNClob(int columnIndex, NClob nClob) throws SQLException;
void updateNClob(String columnLabel, NClob nClob) throws SQLException;
NClob getNClob(int columnIndex) throws SQLException;
NClob getNClob(String columnLabel) throws SQLException;
SQLXML getSQLXML(int columnIndex) throws SQLException;
SQLXML getSQLXML(String columnLabel) throws SQLException;
void updateSQLXML(int columnIndex, SQLXML xmlObject) throws SQLException;
void updateSQLXML(String columnLabel, SQLXML xmlObject) throws SQLException;
String getNString(int columnIndex) throws SQLException;
String getNString(String columnLabel) throws SQLException;
java.io.Reader getNCharacterStream(int columnIndex) throws SQLException;
java.io.Reader getNCharacterStream(String columnLabel) throws SQLException;
void updateNCharacterStream(int columnIndex, java.io.Reader x,
long length) throws SQLException;
void updateNCharacterStream(String columnLabel, java.io.Reader reader,
long length) throws SQLException;
void updateAsciiStream(int columnIndex, java.io.InputStream x,
long length) throws SQLException;
void updateBinaryStream(int columnIndex, java.io.InputStream x,
long length) throws SQLException;
void updateCharacterStream(int columnIndex, java.io.Reader x,
long length) throws SQLException;
void updateAsciiStream(String columnLabel, java.io.InputStream x,
long length) throws SQLException;
void updateBinaryStream(String columnLabel, java.io.InputStream x,
long length) throws SQLException;
void updateCharacterStream(String columnLabel, java.io.Reader reader,
long length) throws SQLException;
void updateBlob(int columnIndex, InputStream inputStream, long length) throws SQLException;
void updateBlob(String columnLabel, InputStream inputStream, long length) throws SQLException;
void updateClob(int columnIndex, Reader reader, long length) throws SQLException;
void updateClob(String columnLabel, Reader reader, long length) throws SQLException;
void updateNClob(int columnIndex, Reader reader, long length) throws SQLException;
void updateNClob(String columnLabel, Reader reader, long length) throws SQLException;
void updateNCharacterStream(int columnIndex, java.io.Reader x) throws SQLException;
void updateNCharacterStream(String columnLabel, java.io.Reader reader) throws SQLException;
void updateAsciiStream(int columnIndex, java.io.InputStream x) throws SQLException;
void updateBinaryStream(int columnIndex, java.io.InputStream x) throws SQLException;
void updateCharacterStream(int columnIndex, java.io.Reader x) throws SQLException;
void updateAsciiStream(String columnLabel, java.io.InputStream x) throws SQLException;
void updateBinaryStream(String columnLabel, java.io.InputStream x) throws SQLException;
void updateCharacterStream(String columnLabel, java.io.Reader reader) throws SQLException;
void updateBlob(int columnIndex, InputStream inputStream) throws SQLException;
void updateBlob(String columnLabel, InputStream inputStream) throws SQLException;
void updateClob(int columnIndex, Reader reader) throws SQLException;
void updateClob(String columnLabel, Reader reader) throws SQLException;
void updateNClob(int columnIndex, Reader reader) throws SQLException;
void updateNClob(String columnLabel, Reader reader) throws SQLException;
//------------------------- JDBC 4.1 -----------------------------------
public <T> T getObject(int columnIndex, Class<T> type) throws SQLException;
public <T> T getObject(String columnLabel, Class<T> type) throws SQLException;
//------------------------- JDBC 4.2 -----------------------------------
default void updateObject(int columnIndex, Object x,
SQLType targetSqlType, int scaleOrLength) throws SQLException {
throw new SQLFeatureNotSupportedException("updateObject not implemented");
}
default void updateObject(String columnLabel, Object x,
SQLType targetSqlType, int scaleOrLength) throws SQLException {
throw new SQLFeatureNotSupportedException("updateObject not implemented");
}
default void updateObject(int columnIndex, Object x, SQLType targetSqlType)
throws SQLException {
throw new SQLFeatureNotSupportedException("updateObject not implemented");
}
default void updateObject(String columnLabel, Object x,
SQLType targetSqlType) throws SQLException {
throw new SQLFeatureNotSupportedException("updateObject not implemented");
}
}
| 方法名称 | 功能描述 |
|---|---|
| getString(int columnIndex) | 用于获取指定字段的String类型的值,参数columnIndex代表字段的索引 |
| getString(columnName) | 用于获职指定字段的String类型的值,参数columnName代表字段的名称 |
| getInt(int columnIndex) | 用于获职指定字段的int类型的值,参数columnIndex代表字段的索引 |
| getInt(String columnName) | 用于获职指定字段的int类型的值,参数columnName代表字段的名称 |
| getDate(int columnIndex) | 用于获职指定字段的Date类型的值,参数columnIndex代表字段的索引 |
| getDate(String columnName) | 用于获取指定字段的Date类型的值,参数columnName代表字段的名称 |
| next() | 将游标从ResultSet对象当前位置 向下移一行 |
| absolute(int row) | 指定行 |
| afterLast() | 最后一行之后 |
| beforeFirst() | 第一行之前 |
| previous() | 上一行 |
| last() | 最后一行 |
3. 实现第一个JDBC程序
参照ch04创建ch10_ls项目、添加框架支持(web)、 Web\WEB-INF新建lib并粘贴MySQL8.0的驱动程序mysql-connector-java-8.0.30.jar、模块中添加Tomcat和数据库驱动程序支持(项目结构 - 模块 - 依赖 - 添加库 - Tomcat，添加jar或目录 - 选Web\WEN-INf\lib)、添加工件、编辑运行配置，创建ch10首页index.jsp
index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>JDBC</title>
</head>
<body>
<h1>ch10 JDBC(ls)</h1>
<hr>
<a href="login_ls.jsp">1. 任务:网站用户登录功能</a><br>
<a href="index_ls.jsp">2. JSP学生信息显示和增改删</a><br>
<hr>
<p><a href="http://101.42.158.247/21javaweb.html">返回课程首页</a>
ls <script>document.write(document.lastModified); </script> 制作</p>
</body>
</html>
第1个JDBC
(1) SQL (可直接在本地或云端创建数据库和数据表)
create database jdbc_ls DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
use jdbc_ls;
CREATE TABLE 用户表(
id INT PRIMARY KEY AUTO_INCREMENT,
用户名 VARCHAR(40),
密码 VARCHAR(40),
电邮 VARCHAR(60),
生日 DATE
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO 用户表(用户名,密码,电邮,生日) VALUES
('ls','1234560','dzj@sina.com','1963-12-04'),
('张三','1234561','zs@sina.com','1980-12-05'),
('李四','1234562','lisi@sina.com','1981-12-06'),
('王五','1234563','wangwu@sina.com','1979-12-07');
SELECT * FROM 用户表;
create table clob表(
id int primary key auto_increment,
简历 text
);
create table blob表(
id int primary key auto_increment,
照片 blob
);
DROP TABLE IF EXISTS `学校表`;
CREATE TABLE `学校表` (
`id` int(11),
`名称` varchar(20)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
(2) src 创建包名 cn.ls, 创建Eg01_ls.java Statement 查询
package cn.ls;
import java.sql.*;
public class Eg01_ls {
public static void main(String[] args) throws SQLException {
// 1. 注册数据库的驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
// 2.通过DriverManager获取数据库连接
String url = "jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false";
String username = "root";//数据库名称
String password = "******";//数据库密码
Connection conn = DriverManager.getConnection(url, username, password);
// 3.通过Connection对象获取Statement对象
Statement stmt = conn.createStatement();
// 4.使用Statement执行SQL语句。
String sql = "select * from 用户表";
ResultSet rs = stmt.executeQuery(sql);
// 5. 操作ResultSet结果集
System.out.println("Statement 【ls】");
System.out.println("id | 用户名 | 密码 | 电邮 | 生日");
while (rs.next()) {
int id = rs.getInt("id"); // 通过列名获取指定字段的值
String name = rs.getString("用户名");
String psw = rs.getString("密码");
String email = rs.getString("电邮");
Date birthday = rs.getDate("生日");
System.out.println(id + " | " + name + " | " + psw + " | " + email
+ " | " + birthday);
}
// 6.回收数据库资源
rs.close();
stmt.close();
conn.close();
}
}
实现JDBC程序需要注意的地方—注册驱动
- MySQL 5.6之前使用Class.forName("com.mysql.jdbc.Driver")；
- MySQL 5.6以及之后 Class.forName("com.mysql.cj.jdbc.Driver")。
4. PreparedStatement、CallableStatement对象
(1) cn.ls, 创建Eg02_ls.java PreparedStatement 插入数据
package cn.ls;
import org.junit.Test;
import java.sql.*;
public class Eg02_ls {
@Test
public void fun02() throws Exception {
Connection conn = null;
PreparedStatement preStmt = null;
try {
// 加载数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false";
String username = "root";//数据库名称
String password = "*******";//数据库密码
// 创建应用程序与数据库连接的Connection对象
conn = DriverManager.getConnection(url, username, password);
// 执行的SQL语句
String sql = "INSERT INTO 用户表(用户名,密码,电邮,生日)" + "VALUES(?,?,?,?)";
// 创建执行SQL语句的PreparedStatement 对象
preStmt = conn.prepareStatement(sql);
preStmt.setString(1, "赵六");
preStmt.setString(2, "1234568");
preStmt.setString(3, "zl@sina.com");
preStmt.setString(4, "1818-5-5");
preStmt.executeUpdate();
System.out.println("【ls】插入数据完成。OK");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally { // 释放资源
if (preStmt != null) {
try {
preStmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
preStmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
}
添加 - JUnit 4。
右键fun02 JUnit运行测试。
(2) cn.ls.Eg02_ls.java中 @Test fun03() CallableStatement 调用存储过程
MySQL创建存储过程
CREATE DEFINER=`root`@`localhost` PROCElsRE `add_pro`(IN a int,in b int, out sum int)
BEGIN
set sum := a + b;
END
public class Eg02_ls {
@Test
public void fun03() throws Exception {
CallableStatement cstmt = null;
Connection conn = null;
try {
// 注册数据库的驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false";
String username = "root";
String password = "**********";
conn = DriverManager.getConnection(url, username, password);
// 使用Connection来创建一个CallableStatement对象
cstmt = conn.prepareCall("call add_pro(?,?,?)");
cstmt.setInt(1, 44);
cstmt.setInt(2, 55);
// 注册CallableStatement的第三个参数为int类型
cstmt.registerOutParameter(3, Types.INTEGER);
// 执行存储过程
cstmt.execute();
System.out.println("【ls】调用存储过程。执行结果是：" + cstmt.getInt(3));
// 回收数据库资源
} finally {
if (cstmt != null) {
cstmt.close();
}
if (conn != null) {
conn.close();
}
}
}
5. ResultSet对象
(1) cn.ls.Eg02_ls.java中 @Test fun04() Statement - ResultSet 结果集滚动
@Test
public void fun04() throws Exception {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false";
String username = "root";
String password = "*******";
conn = DriverManager.getConnection(url, username, password);
String sql = "select * from 用户表 ";
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.executeQuery(sql);
System.out.print("【ls】第2条数据的【用户名】值为:");
rs.absolute(2); // 将指针定位到结果集中第2行数据
System.out.println(rs.getString("用户名"));
System.out.print("【ls】第1条数据的【用户名】值为:");
rs.beforeFirst(); // 将指针定位到结果集中第1行数据之前
rs.next(); // 将指针向后滚动
System.out.println(rs.getString("用户名"));
System.out.print("【ls】最后1条数据的【用户名】值为:");
rs.afterLast(); // 将指针定位到结果集中最后一条数据之后
rs.previous(); // 将指针向前滚动
System.out.println(rs.getString("用户名"));
} catch (Exception e) {
e.printStackTrace();
} finally { // 释放资源
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;
}
}
}
6. 动手实践:使用JDBC
6.1 使用JDBC完成数据的增删改查
(1) src 创建包名 cn.ls.domain, 创建User.java javabean
package cn.ls.domain;
import java.util.Date;
public class User {
private int id;
private String 用户名;
private String 密码;
private String 电邮;
private Date 生日;
// getter | setter
}
(2) src 创建包名 cn.ls.utils, 创建JDBCUtils.java
package cn.ls.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
// 加载驱动，并建立数据库连接
public static Connection getConnection() throws SQLException,
ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false";
String username = "root";
String password = "**********";
Connection conn = DriverManager.getConnection(url, username,
password);
return conn;
}
// 关闭数据库连接，释放资源
public static void release(Statement stmt, Connection conn) {
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;
}
}
public static void release(ResultSet rs, Statement stmt,
Connection conn){
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
release(stmt, conn);
}
}
(3) src 创建包名 cn.ls.dao, 创建UserDao.java
package cn.ls.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import cn.ls.domain.User;
import cn.ls.utils.JDBCUtils;
public class UserDao {
// 添加用户的操作
public boolean insert(User user) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
boolean ret = false;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.get生日());
String sql = "INSERT INTO 用户表(id,用户名,密码,电邮,生日) " + "VALUES(" + user.getId() + ",'" + user.get用户名() + "','"
+ user.get密码() + "','" + user.get电邮() + "','" + birthday + "')";
int num = stmt.executeUpdate(sql);
if (num == 1)
ret = true;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return ret;
}
// 查询所有的User对象
public ArrayList<User> findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
ArrayList<User> list = new ArrayList<User>();
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
String sql = "SELECT * FROM 用户表";
rs = stmt.executeQuery(sql);
// 处理结果集
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.set用户名(rs.getString("用户名"));
user.set密码(rs.getString("密码"));
user.set电邮(rs.getString("电邮"));
user.set生日(rs.getDate("生日"));
list.add(user);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
// 根据id查找指定的user
public User find(int id) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
String sql = "SELECT * FROM 用户表 WHERE id=" + id;
rs = stmt.executeQuery(sql);
// 处理结果集
if (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.set用户名(rs.getString("用户名"));
user.set密码(rs.getString("密码"));
user.set电邮(rs.getString("电邮"));
user.set生日(rs.getDate("生日"));
return user;
}
return null;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return null;
}
// 删除用户
public boolean delete(int id) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
String sql = "DELETE FROM 用户表 WHERE id=" + id;
int num = stmt.executeUpdate(sql);
if (num == 1) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
// 修改用户
public boolean update(User user) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 获得数据的连接
conn = JDBCUtils.getConnection();
// 获得Statement对象
stmt = conn.createStatement();
// 发送SQL语句
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(user.get生日());
String sql = "UPDATE 用户表 set 用户名='" + user.get用户名() + "',密码='" + user.get密码() + "',电邮='" + user.get电邮()
+ "',生日='" + birthday + "' WHERE id=" + user.getId();
int num = stmt.executeUpdate(sql);
if (num > 0) {
return true;
}
return false;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
return false;
}
}
(4) cn.ls, 创建Eg03_ls.java
package cn.ls;
import java.util.ArrayList;
import java.util.Date;
import org.junit.Test;
import cn.ls.dao.UserDao;
import cn.ls.domain.User;
public class Eg03_ls {
@Test
public void fun05() throws Exception {
UserDao ud = new UserDao();
User user = new User();
user.setId(6);
user.set用户名("今生");
user.set密码("123");
user.set电邮("js@sina.com");
user.set生日(new Date());
boolean b = ud.insert(user);
System.out.println("=====【ls】JDBC基本操作:======");
System.out.println("【ls】insert 向[用户表]表插入一个用户信息:"+b);
};
@Test
public void fun06() throws Exception {
UserDao ud = new UserDao();
ArrayList<User> list = ud.findAll();
System.out.println("【ls】findAll:");
for (int i = 0; i < list.size(); i++) {
System.out.println("第" + (i + 1) + "条数据的【用户名】值为:" + list.get(i).get用户名());
}
};
@Test
public void fun07() throws Exception {
UserDao ud = new UserDao();
System.out.println("【ls】find:");
User user = ud.find(1);
System.out.println("id为1的User对象的【用户名】值为：" + user.get用户名());
user = ud.find(3);
System.out.println("id为3的User对象的【用户名】值为：" + user.get用户名());
};
@Test
public void fun08() throws Exception {
// 修改User对象的数据
UserDao ud = new UserDao();
User user = new User();
user.setId(4);
user.set用户名("赵小六");
user.set密码("666666");
user.set电邮("zhaoxiaoliu@sina.com");
user.set生日(new Date());
boolean b = ud.update(user);
System.out.println("【ls】update更新用户数据："+b);
};
@Test
public void fun09() throws Exception {
// 删除操作
UserDao ud = new UserDao();
boolean b = ud.delete(4);
System.out.println("【ls】delete删除用户数据："+b);
};
}
6.2 使用JDBC完成批处理
(1) cn.ls, 创建Eg04_ls.java
package cn.ls;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Statement;
import org.junit.Test;
import cn.ls.utils.JDBCUtils;
public class Eg04_ls {
@Test
public void fun10() throws Exception {
Connection conn = null;
Statement stmt = null;
try {
// 加载数据库驱动
conn = JDBCUtils.getConnection();
stmt = conn.createStatement();
// SQL语句
String sql1 = "DROP TABLE IF EXISTS 学校表;";
String sql2 = "CREATE TABLE 学校表(id int,校名 varchar(20))";
String sql3 = "INSERT INTO 学校表 VALUES(2,'山西传媒大学')";
String sql4 = "UPDATE 学校表 SET id = 1";
// Statement批处理sql语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
stmt.addBatch(sql4);
stmt.executeBatch();
System.out.println("【ls】批处理语句执行成功。");
} catch (Exception e) {
e.printStackTrace();
} finally { // 释放资源
JDBCUtils.release(null, stmt, conn);
};
};
@Test
public void fun11() throws Exception {
Connection conn = null;
PreparedStatement preStmt = null;
try {
// 加载并注册数据库驱动
conn=JDBCUtils.getConnection();
String sql = "INSERT INTO 用户表(用户名,密码,电邮,生日)"
+ "VALUES(?,?,?,?)";
preStmt = conn.prepareStatement(sql);
for (int i = 1; i <= 5; i++) {
preStmt.setString(1, "杜小" + i);
preStmt.setString(2, "mm666888" + i);
preStmt.setString(3, "hc" + i + "@hc.cn");
preStmt.setDate(4, Date.valueOf("1989-02-19"));
preStmt.addBatch();
}
preStmt.executeBatch();
System.out.println("【ls】5条信息成批插入成功。");
} catch (Exception e) {
e.printStackTrace();
} finally { // 释放资源
JDBCUtils.release(null, preStmt, conn);
}
}
}
6.3 使用JDBC完成大数据处理
(1) clob准备 e:\钟南山.txt
钟南山（中国工程院院士、著名呼吸病学专家）
钟南山，男，汉族，中共党员，1936年10月出生于江苏南京，福建厦门人，呼吸内科学家，广州医科大学附属第一医院国家呼吸系统疾病临床医学研究中心主任，中国工程院院士[1]，中国医学科学院学部委员[2]，中国抗击非典型肺炎的领军人物，曾任广州医学院院长、党委书记，广州市呼吸疾病研究所所长、广州呼吸疾病国家重点实验室主任、中华医学会会长。[3-6]国家卫健委高级别专家组组长、[7]国家健康科普专家。[8]
钟南山出生于医学世家；1958年8月，在第一届全运会的比赛测验中，钟南山以54秒2的成绩，打破了当时54秒6的400米栏全国纪录[9]。1960年毕业于北京医学院（今北京大学医学部）；2007年获英国爱丁堡大学荣誉博士；2007年10月任呼吸疾病国家重点实验室主任；2014年获香港中文大学荣誉理学博士[10]；2019年被聘为中国医学科学院学部委员[2]；2020年8月11日，习近平签署主席令，授予钟南山“共和国勋章”[11]；9月4日，钟南山入选2020年“全国教书育人楷模”名单[12]。9月3日，入选世卫组织新冠肺炎疫情应对评估专家组名单；[13]11月3日，钟南山被授予2020年度何梁何利基金“科学与技术成就奖”。[14]
钟南山长期致力于重大呼吸道传染病及慢性呼吸系统疾病的研究、预防与治疗，成果丰硕，实绩突出。[1][15]
人物履历
1936年10月，钟南山出生于南京，父亲钟世藩是中国著名的儿科专家，母亲廖月琴则是广东省肿瘤医院的创始人之一。[16]
1953年，就读于华南师范学院附属中学（现已演变成两所学校广东实验中学、华南师范大学附属中学）。
1960年，毕业于北京医学院（现北京大学医学部）并留校任教。
1960年7月至1971年8月，担任北京医学院（现北京大学医学部）放射医学教研组助教。
1971年9月至1974年4月，担任广州医学院第一附属医院内科住院医师。
1974年4月至1978年8月，担任广州医学院第一附属医院内科主治医师。
1978年9月至1982年12月，担任广州医学院第一附属医院内科副主任医师、讲师。
1979年至1981年，赴英国爱丁堡大学医学院及伦敦大学呼吸系进修。[15]
1983年1月至1986年12月，担任广州医学院副教授（于1985年后被指定为中央领导保健医生）。[17]
1986年12月至1995年，担任广州医学院呼吸内科教授、硕士生导师。
1987年12月至1992年10月，担任广州医学院第一附属医院院长。[18]
1992年5月至2002年12月，钟南山担任广州医学院院长、党委书记。[17]
1992年2月至1997年12月广州医学院附一院广州呼吸疾病研究所副研究员[19]
1995年8月，担任北京大学医学部呼吸内科教授、博士生导师。
1995年10月至1996年10月，公派到加拿大蒙特利尔市麦吉尔大学访问学者。
1996年5月，当选为中国工程院院士。
1997年12月至1999年12月广州医学院附一院广州呼吸疾病研究所主任医师。
1999年12月至2012年10月广州医学院附一院广州呼吸疾病研究所教授/副所长。[19]
1998年6月，任中国工程院医药卫生工程学部副主任。
2003年，“非典”爆发，以钟南山为代表的医护工作者经长期努力，抗击了非典。
2004年，被评为“感动中国2003年度”十大人物之一。
2005年4月13日，当选中华医学会第23届会长。
2007年，获英国爱丁堡大学荣誉博士学位。
2007年10月，担任呼吸疾病国家重点实验室主任。
2009年7月，担任贵州医科大学名誉院长。
2009年9月10日，被评为“100位新中国成立以来感动中国人物”。[20]
2010年，获澳门科技大学荣誉博士学位。
2012年10至2013年7月，任广州医学院附一院广州呼吸疾病研究所教授/常务副所长。[18]
2013年7月至今，任广州呼吸疾病研究所所长。[18-19]
2014年，获颁香港中文大学荣誉理学博士学位。[10]
2020年1月20日，国家卫健委高级别专家组召开发布会，钟南山任专家组组长。[7][21]
2020年1月21日，新型冠状病毒联防联控工作机制科研攻关组第一次会议，钟南山任组长。[22]
2020年9月3日，世卫组织新冠肺炎疫情应对评估专家组共同主席在成员国吹风会上宣布了专家组成员名单，钟南山入选。[13]
第十一、十二届全国人大代表，第八、九、十届全国政协委员[1]。
(2) blob准备 e:\钟南山.jpg
(3) 创建cn.ls.Eg05_ls.java
package cn.ls;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import cn.ls.utils.JDBCUtils;
public class Eg05_ls {
@Test
public void CLOBDemo01() throws Exception {
Connection conn = null;
PreparedStatement preStmt = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into clob表 values(?,?)";
preStmt = conn.prepareStatement(sql);
File file = new File("e:\\钟南山.txt");
Reader reader = new InputStreamReader(new FileInputStream(file), "utf-8");
preStmt.setInt(1, 1);
preStmt.setCharacterStream(2, reader, (int) file.length());
preStmt.executeUpdate();
System.out.println("【ls】上传文本到数据库clob字段。写入成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
JDBCUtils.release(null, preStmt, conn);
}
}
@Test
public void CLOBDemo02() throws Exception {
Connection conn = null;
PreparedStatement preStmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from clob表";
preStmt = conn.prepareStatement(sql);
rs = preStmt.executeQuery();
if (rs.next()) {
Reader reader = rs.getCharacterStream("简历");
Writer out = new FileWriter("e:\\钟南山resume.txt");
int temp;
while ((temp = reader.read()) != -1) {
out.write(temp);
}
out.close();
reader.close();
}
System.out.println("【ls】读取数据库clob字段写入[e:\\钟南山resume.txt]成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
JDBCUtils.release(rs, preStmt, conn);
}
}
@Test
public void BLOBDemo01() throws Exception {
Connection conn = null;
PreparedStatement prestmt = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into blob表 values(?,?)";
prestmt = conn.prepareStatement(sql);
prestmt.setInt(1, 1);
File file = new File("e:\\钟南山.jpg");
InputStream in = new FileInputStream(file);
prestmt.setBinaryStream(2, in, (int) file.length());
prestmt.executeUpdate();
System.out.println("【ls】上传照片写入数据库blob字段成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放资源
JDBCUtils.release(null, prestmt, conn);
}
}
@Test
public void BLOBDemo02() throws Exception {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from blob表 where id = 1";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
if (rs.next()) {
InputStream in = new BufferedInputStream(rs.getBinaryStream("照片"));
OutputStream out = new BufferedOutputStream(new FileOutputStream("e:\\钟南山img.jpg"));
int temp;
while ((temp = in.read()) != -1) {
out.write(temp);
}
out.close();
in.close();
System.out.println("【ls】从数据库blob字段下载照片写入文件[e:\\钟南山img.jpg]成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.release(rs, stmt, conn);
}
}
}
7. 任务:网站用户登录功能
(1) 创建【账户表】并填入数据
CREATE TABLE 账户表 (
id INT NOT NULL AUTO_INCREMENT primary key,
账户名 VARCHAR(40) NOT NULL unique,-- 账户名称，唯一键、设置不为空
密码 VARCHAR(40) NOT NULL,
姓名 VARCHAR(40) NOT NULL,
性别 VARCHAR(1) DEFAULT '',
手机号 VARCHAR(30) NOT NULL unique, -- 唯一
身份证号 VARCHAR(30) NOT NULL unique -- 唯一
);
INSERT INTO 账户表 VALUES(1,'dzjiang','123456','ls','男','13888888888','110202107075023');
(2) 登录界面 web/login_ls.jsp
<%@ page contentType="text/html;charset=UTF-8"%>
<html>
<head>
<title>登录页面</title>
</head>
<style>
* {
margin: 0;
padding: 0;
}
form {
display: block;
height: auto;
width: 450px;
margin: 100px auto;
}
form table tr {
height: 40px;
}
form table tr td {
height: 40px;
width: 280px;
line-height: 40px;
}
form table tr td input {
height: 32px;
border: 1px solid #BABABA;
border-radius: 6px;
}
.alignRight {
text-align: right;
line-height: 40px;
font-size: 16px;
font-family: "Monaco";
width: 200px;
}
.submit {
display: block;
height: 40px;
width: 250px;
color: white;
font-weight: bold;
font-size: 18px;
background-color: #98ECAC;
border-radius: 8px;
margin: 15px auto;
}
</style>
<body>
<form action="LoginServlet" method="post">
<table>
<tr>
<td class="alignRight">
账户名:
</td>
<td>
<input type="text" name="用户名" />
</td>
</tr>
<tr>
<td class="alignRight">
密码:
</td>
<td>
<input type="password" name="密码" />
</td>
</tr>
</table>
<input type="submit" value="登 录" class="submit" />
</form>
</body>
</html>
(3) 获取数据库连接 cn/ls/login/GetConnection.java
package cn.ls.login;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class GetConnection {
static Connection conn = null;
public static Connection getConnection() throws ClassNotFoundException {
String driver="com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://101.42.158.247:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false";
String user = "root";//数据库用户名
String password = "********";//数据库密码
Class.forName(driver);
try {
conn = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
//返回Connection对象
return conn;
}
}
(4) 登录处理
package cn.ls.login;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse
response)throws ServletException, IOException {
//设置请求编码、响应方式和编码方式
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html");
PrintWriter out = response.getWriter();
Connection conn = null;
ResultSet rs = null;
PreparedStatement ptst = null;
//获取登录页面提交的数据
String loginName = request.getParameter("用户名");
String loginPassword = request.getParameter("密码");
//sql语句, 此处账户名 唯一
String selectPassword = "select 密码 from 账户表 where 账户名 = ?";
try {
//获取与数据库的链接
conn = GetConnection.getConnection();
ptst = (PreparedStatement)
conn.prepareStatement(selectPassword);
//设置ptst参数
ptst.setString(1, loginName);
rs = ptst.executeQuery();
if (rs.next()) {
//判断数据库与登录页面提交的password是否一致
if (rs.getString(1).equals(loginPassword)) {
out.println("欢迎登录。");
} else {
out.println("密码错误，请重新输入。");
}
}else {
out.println("账户名不存在");
}
} catch (SQLException | ClassNotFoundException ex) {
throw new RuntimeException(ex);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ptst != null) {
try {
ptst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭链接
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
out.flush();
out.close();
}
}
8. JSP学生信息显示和增改删
(1) 云端MySQL
create table 班级表_ls(
班级ID int auto_increment not null primary key ,
班级名称 nvarchar(20) not null
)engine = InnoDB auto_increment = 300001;
create table 学生表_ls(
学号 varchar(10) not null primary key,
班级ID int not null ,
姓名 varchar(10) not null,
性别 enum('男','女') not null,
生日 date not null,
身高 decimal(15, 2) not null,
政治面貌 enum('中共党员','民主人士','共青团员','群众') not null,
注册时间 timestamp null default current_timestamp on update current_timestamp,
foreign key (班级ID) references 班级表_ls(班级ID)
)engine = InnoDB;
insert 班级表_ls values
(302102,'21专升本2班'),
(302103,'21专升本3班');
insert 学生表_ls(班级ID, 学号, 姓名, 性别, 生日, 身高, 政治面貌, 注册时间) values
(302102, '217201', '陈梅', '女', '1999.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302102, '217202', '程鹏', '男', '1999.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302102, '217203', '程帅', '男', '1999.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302102, '217204', '崔君仙', '女', '2000.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302102, '217205', '杜晓春', '女', '2000.08.01',1.72,'共青团员','2021.08.28 10:34:16'),
(302102, '217206', '高彬', '男', '2001.08.01',1.85,'共青团员','2021.08.28 10:34:16'),
(302102, '217207', '谷坤', '男', '1999.08.01',1.68,'共青团员','2021.08.28 10:34:16'),
(302102, '217208', '郭大彪', '男', '2000.08.01',1.92,'共青团员','2021.08.28 10:34:16'),
(302102, '217209', '郭晓丽', '女', '1999.08.01',1.73,'共青团员','2021.08.28 10:34:16'),
(302102, '217210', '郝俊杰', '男', '1999.08.01',1.64,'共青团员','2021.08.28 10:34:16'),
(302102, '217211', '胡昕', '女', '1998.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302102, '217212', '贾瑞宁', '男', '2000.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302102, '217213', '贾世笑', '男', '1999.08.01',1.50,'共青团员','2021.08.28 10:34:16'),
(302102, '217214', '焦磊', '男', '2000.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302102, '217215', '兰宝宝', '男', '1999.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302102, '217216', '李波', '男', '1999.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302102, '217217', '李伟', '男', '1998.08.01',1.81,'共青团员','2021.08.28 10:34:16'),
(302102, '217218', '李扬帆', '女', '1999.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302102, '217219', '李媛媛', '女', '1998.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302102, '217220', '李振宇', '男', '1999.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302102, '217221', '梁宇轩', '男', '1998.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302102, '217222', '刘淑华', '男', '1999.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302102, '217223', '吕晨', '男', '2000.08.01',1.72,'共青团员','2021.08.28 10:34:16'),
(302102, '217224', '吕瑞林', '男', '2000.08.01',1.85,'共青团员','2021.08.28 10:34:16'),
(302102, '217225', '马国壮', '男', '2000.08.01',1.68,'共青团员','2021.08.28 10:34:16'),
(302102, '217226', '裴晓璐', '女', '2000.08.01',1.92,'共青团员','2021.08.28 10:34:16'),
(302102, '217227', '秦国靖', '男', '1998.08.01',1.73,'共青团员','2021.08.28 10:34:16'),
(302102, '217228', '任续超', '男', '1998.08.01',1.64,'共青团员','2021.08.28 10:34:16'),
(302102, '217229', '申曜瑞', '男', '2000.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302102, '217230', '史振东', '男', '2000.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302102, '217231', '司鑫亮', '男', '2000.08.01',1.50,'共青团员','2021.08.28 10:34:16'),
(302102, '217232', '孙如琦', '女', '2000.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302102, '217233', '王从容', '男', '2000.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302102, '217234', '王青云', '男', '2000.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302102, '217235', '吴婉婷', '女', '2000.08.01',1.81,'共青团员','2021.08.28 10:34:16'),
(302102, '217236', '吴雅丹', '女', '1999.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302102, '217237', '武浩洋', '男', '1998.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302102, '217238', '武佳杰', '男', '2000.08.01',1.72,'共青团员','2021.08.28 10:34:16'),
(302102, '217239', '邢嘉楠', '男', '1999.08.01',1.85,'共青团员','2021.08.28 10:34:16'),
(302102, '217240', '胥福宝', '男', '2000.08.01',1.68,'共青团员','2021.08.28 10:34:16'),
(302102, '217241', '徐淑珍', '女', '2000.08.01',1.92,'共青团员','2021.08.28 10:34:16'),
(302102, '217242', '闫鑫勇', '男', '1998.08.01',1.73,'共青团员','2021.08.28 10:34:16'),
(302102, '217243', '闫展飞', '男', '2000.08.01',1.64,'共青团员','2021.08.28 10:34:16'),
(302102, '217244', '杨韶东', '男', '1999.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302102, '217245', '袁婷', '女', '1999.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302102, '217246', '张江涛', '男', '2000.08.01',1.50,'共青团员','2021.08.28 10:34:16'),
(302102, '217247', '张英', '女', '2000.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302102, '217248', '赵辰辰', '男', '2000.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302102, '217249', '赵锦涛', '男', '2000.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302102, '217250', '赵腾', '男', '2000.08.01',1.81,'共青团员','2021.08.28 10:34:16'),
(302102, '217251', '朱卫东', '男', '2000.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302103, '217301', '车斌鑫', '男', '1999.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302103, '217302', '车鸿辉', '男', '2000.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302103, '217303', '陈睿清', '女', '2000.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302103, '217304', '崔亚兵', '男', '2000.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302103, '217305', '董方圆', '男', '1999.08.01',1.72,'共青团员','2021.08.28 10:34:16'),
(302103, '217306', '段杰', '男', '2000.08.01',1.85,'共青团员','2021.08.28 10:34:16'),
(302103, '217307', '冯威铭', '男', '2000.08.01',1.68,'共青团员','2021.08.28 10:34:16'),
(302103, '217308', '古俊凯', '男', '1999.08.01',1.92,'共青团员','2021.08.28 10:34:16'),
(302103, '217309', '郭成龙', '男', '2001.08.01',1.73,'共青团员','2021.08.28 10:34:16'),
(302103, '217310', '郭晶晶', '女', '2000.08.01',1.64,'共青团员','2021.08.28 10:34:16'),
(302103, '217311', '郭芝祎', '女', '2000.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302103, '217312', '郝澳桐', '女', '1999.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302103, '217313', '郝江伟', '男', '1999.08.01',1.50,'共青团员','2021.08.28 10:34:16'),
(302103, '217314', '郝翁康', '男', '1999.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302103, '217315', '贾杰顺', '男', '1999.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302103, '217316', '贾进山', '男', '1998.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302103, '217317', '李凡', '男', '2000.08.01',1.81,'共青团员','2021.08.28 10:34:16'),
(302103, '217318', '梁晨', '男', '2003.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302103, '217319', '梁晨露', '男', '2000.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302103, '217320', '刘富建', '男', '1998.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302103, '217321', '刘帅琦', '男', '1998.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302103, '217322', '刘思林', '男', '1999.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302103, '217323', '刘伟杰', '男', '2001.08.01',1.72,'共青团员','2021.08.28 10:34:16'),
(302103, '217324', '刘晓琴', '女', '1997.08.01',1.85,'共青团员','2021.08.28 10:34:16'),
(302103, '217325', '刘乙丙', '男', '1999.08.01',1.68,'共青团员','2021.08.28 10:34:16'),
(302103, '217326', '刘懿', '女', '2001.08.01',1.92,'共青团员','2021.08.28 10:34:16'),
(302103, '217327', '雒小雨', '女', '1999.08.01',1.73,'共青团员','2021.08.28 10:34:16'),
(302103, '217328', '马天培', '男', '1999.08.01',1.64,'共青团员','2021.08.28 10:34:16'),
(302103, '217329', '孟贤磊', '男', '1999.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302103, '217330', '苗宏亮', '男', '1999.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302103, '217331', '牛烨灵', '男', '1999.08.01',1.50,'共青团员','2021.08.28 10:34:16'),
(302103, '217332', '秦咪雪', '女', '1999.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302103, '217333', '孙佳惠', '女', '2000.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302103, '217334', '孙凯', '男', '2000.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302103, '217335', '王斌', '男', '1999.08.01',1.81,'共青团员','2021.08.28 10:34:16'),
(302103, '217336', '王浩轩', '男', '2000.08.01',1.65,'共青团员','2021.08.28 10:34:16'),
(302103, '217337', '王鑫涛', '男', '2000.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302103, '217338', '卫婷婷', '女', '2000.08.01',1.72,'共青团员','2021.08.28 10:34:16'),
(302103, '217339', '魏春燕', '女', '1997.08.01',1.85,'共青团员','2021.08.28 10:34:16'),
(302103, '217340', '温舒鑫', '女', '1999.08.01',1.68,'共青团员','2021.08.28 10:34:16'),
(302103, '217341', '杨红心', '男', '2000.08.01',1.92,'共青团员','2021.08.28 10:34:16'),
(302103, '217342', '杨江涛', '男', '2000.08.01',1.73,'共青团员','2021.08.28 10:34:16'),
(302103, '217343', '杨宇航', '男', '1997.08.01',1.64,'共青团员','2021.08.28 10:34:16'),
(302103, '217344', '张彩霞', '女', '2000.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302103, '217345', '张恒瑞', '男', '2000.08.01',1.70,'共青团员','2021.08.28 10:34:16'),
(302103, '217346', '张佳伟', '男', '2000.08.01',1.50,'共青团员','2021.08.28 10:34:16'),
(302103, '217347', '张妍', '女', '1999.08.01',1.60,'共青团员','2021.08.28 10:34:16'),
(302103, '217348', '张泽斌', '男', '1999.08.01',1.80,'共青团员','2021.08.28 10:34:16'),
(302103, '217349', '郑涛', '男', '1997.08.01',1.83,'共青团员','2021.08.28 10:34:16'),
(302103, '217350', '周晨辉', '男', '1999.08.01',1.65,'共青团员','2021.08.28 10:34:16');
create view 学生视图_ls
as
select 学生表_ls.班级ID, 学号, 姓名, 性别, 生日, year(from_days( datediff( now( ), 生日))) as 年龄, 身高, 政治面貌, 注册时间, 班级名称
from 学生表_ls inner join 班级表_ls on 学生表_ls.班级ID = 班级表_ls.班级ID;
CREATE PROCElsRE `Get学生信息By学号_ls`(in _学号 int)
begin
select 班级ID, 学号, 姓名, 性别, 生日, 年龄, 身高, 政治面貌, 注册时间, 班级名称 from 学生视图_ls where (学号 = _学号);
end
(2) Web\index_ls.jsp
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生列表</title>
</head>
<body>
<h1>学生列表</h1>
<hr>
<a href="StudentAdd_ls.html">新增学生</a><br>
<table style="width:1200px;" border="1" cellspacing="0">
<caption>学生列表</caption>
<tr>
<th>班级名称</th>
<th>学号</th>
<th>姓名</th>
<th>性别</th>
<th>生日</th>
<th>年龄</th>
<th>身高</th>
<th>政治面貌</th>
<th>注册时间</th>
<th>删除</th>
<th>更新</th>
</tr>
<%
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false", "root", "********");
Statement stmt = conn.createStatement();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select 学号, 姓名, 性别, 生日, 年龄, 身高, 政治面貌, 注册时间, 班级名称 from 学生视图_ls");
while (rs.next()) {
out.println("<tr>");
out.println("<td>" + rs.getString("班级名称") + "</td>");
out.println("<td>" + rs.getString("学号") + "</td>");
out.println("<td>" + rs.getString("姓名") + "</td>");
out.println("<td>" + rs.getString("性别") + "</td>");
out.println("<td>" + rs.getString("生日") + "</td>");
out.println("<td>" + rs.getString("年龄") + "</td>");
out.println("<td>" + rs.getString("身高") + "</td>");
out.println("<td>" + rs.getString("政治面貌") + "</td>");
out.println("<td>" + rs.getString("注册时间") + "</td>");
out.println("<td><a href='StudentDelete_ls.jsp?stuNO=" + rs.getString("学号") + "'>删除</a></td>");
out.println("<td><a href='StudentUpdate_ls.jsp?stuNO=" + rs.getString("学号") + "'>更新</a></td>");
out.println("</tr>");
}
//关闭连接、释放资源
rs.close();
stmt.close();
con.close();
%>
</table>
<hr>
</body>
</html>
(3) Web\form.css
@charset "utf-8";
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
color: #666666;
background: #fff;
text-align: center;
}
* {
margin: 0;
padding: 0;
}
a {
color: #1E7ACE;
text-decoration: none;
}
a:hover {
color: #000;
text-decoration: underline;
}
h3 {
font-size: 14px;
font-weight: bold;
}
pre, p {
color: #1E7ACE;
margin: 4px;
}
input, select, textarea {
padding: 1px;
margin: 2px;
font-size: 11px;
}
.buttom {
padding: 1px 10px;
font-size: 12px;
border: 1px #1E7ACE solid;
background: #D0F0FF;
}
#formwrapper {
width: 300px;
margin: 15px auto;
padding: 20px;
text-align: left;
border: 1px solid #A4CDF2;
}
fieldset {
padding: 10px;
margin-top: 5px;
border: 1px solid #A4CDF2;
background: #fff;
}
fieldset legend {
color: #1E7ACE;
font-weight: bold;
padding: 3px 20px 3px 20px;
border: 1px solid #A4CDF2;
background: #fff;
}
fieldset label {
float: left;
width: 120px;
text-align: right;
padding: 4px;
margin: 1px;
}
fieldset div {
clear: left;
margin-bottom: 2px;
}
.input { width: 120px; }
.enter { text-align: center; }
(4) Web StudentAdd_ls.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加学生信息</title>
<link rel="stylesheet" href="form.css">
</head>
<body>
<div id="formwrapper">
<form action="StudentAdd_ls.jsp" method="post">
<fieldset>
<legend>添加学生</legend>
<div><label for="stuNO">学号：</label>
<input type="text" name="stuNO" class="input" id="stuNO" size="20" maxlength="12" placeholder="(最多12个字符)" required>
</div>
<div><label for="Name">姓名：</label>
<input type="text" name="Name" class="input" id="Name" size="20" maxlength="8" title="请输入2至8个汉字" pattern="[\u4e00-\u9fa5]{2,8}" placeholder="(最多8个字)" required>
</div>
<div>
<label for="gender">性别：</label>
<input type="radio" name="gender" value="男" checked="checked">男
<input type="radio" name="gender" value="女">女
</div>
<div>
<label for="birthday">生日：</label>
<input type="date" name="birthday" class="input" id="birthday" size="20" required>
</div>
<div>
<label for="stature">身高：</label>
<input type="number" name="stature" class="input" id="stature" size="20" maxlength="4" max="1.99" min="1.20" step="0.01" title="请输入1.20-1.99之间的数值(m)" required>
</div>
<div>
<label for="political">政治面貌：</label>
<select id="political" name="political" class="input">
<option value="中共党员">中共党员</option>
<option value="民主人士">民主人士</option>
<option value="共青团员">共青团员</option>
<option value="群众">群众</option>
</select>
</div>
<div>
<label for="classNo">班级名称</label>
<select id="classNo" name="classNo" class="input">
<option value="302102">21专升本2班</option>
<option value="302103">21专升本3班</option>
</select>
</div>
<div class="enter">
<input type="submit" class="buttom" value="添加">
<input type="reset" class="buttom" value="重置">
</div>
</fieldset>
</form>
</div>
</body>
</html>
(5) Web StudentAdd_ls.jsp
<%@ page import="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("utf-8"); //设置参数编码格式
String stuNO = request.getParameter("stuNO");
String name = request.getParameter("Name");
String birthday = request.getParameter("birthday");
String gender = request.getParameter("gender");
String stature = request.getParameter("stature");
String political = request.getParameter("political");
String classNo = request.getParameter("classNo");
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false", "root", "*******");
String addSQL = "insert 学生表_ls(班级ID, 学号, 姓名, 性别, 生日, 身高, 政治面貌) values(?,?,?,?,?,?,?)";
PreparedStatement pstmt = null; //声明预处理对象
try {
pstmt = conn.prepareStatement(addSQL); //获得预处理对象并赋值
pstmt.setInt(1, Integer.parseInt(classNo)); //设置第1个参数
pstmt.setString(2, stuNO); //设置第2个参数
pstmt.setString(3, name);
pstmt.setString(4, gender);
pstmt.setString(5, birthday);
pstmt.setFloat(6, Float.parseFloat(stature));
pstmt.setString(7, political);
pstmt.executeUpdate(); //执行更新
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭连接、释放资源
pstmt.close();
conn.close();
}
response.sendRedirect("index_ls.jsp");
%>
(6) Web\StudentUpdate_ls.jsp
<%@ page import="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("utf-8"); //设置参数编码格式
String no=request.getParameter("stuNO"); //获取id参数的值
String classId = "";
String stuName = "";
String gender = "";
String birthday = "";
String stature = "";
String political = "";
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false", "root", "******");
CallableStatement cstmt = null; //声明预处理对象
cstmt = conn.prepareCall("call Get学生信息By学号_ls(?)");
cstmt.setString(1, no); //设置第一个个参数
ResultSet rs = cstmt.executeQuery();
if (rs.next()) {
classId = rs.getString("班级ID");
stuName = rs.getString("姓名");
gender = rs.getString("性别");
birthday = rs.getString("生日");
stature = rs.getString("身高");
political = rs.getString("政治面貌");
}
//关闭连接、释放资源
rs.close();
cstmt.close();
con.close();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改学生信息</title>
<link rel="stylesheet" href="form.css">
</head>
<body>
<div id="formwrapper">
<form action="StudentUpdate_ls_.jsp" method="post">
<fieldset>
<legend>修改学生</legend>
<div><label for="stuNO">学号：</label>
<input type="text" name="stuNO" class="input" id="stuNO" size="20" maxlength="12" placeholder="(最多12个字符)" value="<%=no%>" readonly>
</div>
<div><label for="Name">姓名：</label>
<input type="text" name="Name" class="input" id="Name" size="20" maxlength="8" title="请输入2至8个汉字" pattern="[\u4e00-\u9fa5]{2,8}" value="<%=stuName %>" placeholder="(最多8个字)" required>
</div>
<div>
<label for="gender">性别：</label>
<input type="radio" name="gender" value="男" <% if("男".equals(gender)){%> <%="checked"%> <%}%>>男
<input type="radio" name="gender" value="女" <% if("女".equals(gender)){%> <%="checked"%> <%}%>>女
</div>
<div>
<label for="birthday">生日：</label>
<input type="date" name="birthday" class="input" id="birthday" size="20" value="<%=birthday %>" required>
</div>
<div>
<label for="stature">身高：</label>
<input type="number" name="stature" class="input" id="stature" size="20" maxlength="4" max="1.99" min="1.20" step="0.01" title="请输入1.20-1.99之间的数值(m)" value="<%=stature %>" required>
</div>
<div>
<label for="political">政治面貌：</label>
<select id="political" name="political" class="input">
<option value="中共党员" <% if("中共党员".equals(political)){%> <%="selected"%> <%}%> >中共党员</option>
<option value="民主人士" <% if("民主人士".equals(political)){%> <%="selected"%> <%}%> >民主人士</option>
<option value="共青团员" <% if("共青团员".equals(political)){%> <%="selected"%> <%}%> >共青团员</option>
<option value="群众" <% if("群众".equals(political)){%> <%="selected"%> <%}%> >群众</option>
</select>
</div>
<div>
<label for="classNo">班级名称</label>
<select id="classNo" name="classNo" class="input">
<option value="302102" <% if("302102".equals(classId)){%> <%="selected"%> <%}%> >21数媒专升本2班</option>
<option value="302103" <% if("302103".equals(classId)){%> <%="selected"%> <%}%> >21数媒专升本3班</option>
</select>
</div>
<div class="enter">
<input type="submit" class="buttom" value="修改">
<input type="reset" class="buttom" value="重置">
</div>
</fieldset>
</form>
</div>
</body>
</html>
(7) Web\StudentUpdate_ls＿.jsp
<%@ page mport="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("utf-8"); //设置参数编码格式
String stuNO = request.getParameter("stuNO");
String name = request.getParameter("Name");
String birthday = request.getParameter("birthday");
String gender = request.getParameter("gender");
String stature = request.getParameter("stature");
String political = request.getParameter("political");
String classNo = request.getParameter("classNo");
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false", "root", "*****");
String addSQL = "update 学生表_ls set 班级ID = ?, 姓名=?, 性别=?, 生日=?, 身高=?, 政治面貌=? where 学号=?";
PreparedStatement pstmt = null; //声明预处理对象
try {
pstmt = conn.prepareStatement(addSQL); //获得预处理对象并赋值
pstmt.setInt(1, Integer.parseInt(classNo)); //设置第1个参数
pstmt.setString(2, name);
pstmt.setString(3, gender);
pstmt.setString(4, birthday);
pstmt.setFloat(5, Float.parseFloat(stature));
pstmt.setString(6, political);
pstmt.setString(7, stuNO);
pstmt.executeUpdate(); //执行更新
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭连接、释放资源
pstmt.close();
conn.close();
}
response.sendRedirect("index_ls.jsp");
%>
(8) Web\StudentDelete_ls＿.jsp
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Connection" %>
<%@ page contentType="text/html;charset=UTF-8" %>
<%
request.setCharacterEncoding("utf-8"); //设置参数编码格式
String no=request.getParameter("stuNO"); //获取id参数的值
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://公网ip:3306/jdbc_ls?serverTimezone=GMT%2B8&characterEncoding=UTF-8&useSSL=false", "root", "******");
String deleteSQL = "delete from 学生表_ls where 学号=?";
PreparedStatement pstmt = null; //声明预处理对象
try {
pstmt = conn.prepareStatement(deleteSQL); //获得预处理对象并赋值
pstmt.setString(1, no); //设置第一个个参数
pstmt.executeUpdate(); //执行更新
} catch (SQLException e) {
e.printStackTrace();
} finally{
//关闭连接、释放资源
pstmt.close();
conn.close();
}
response.sendRedirect("index_ls.jsp");
%>
