JDBC入门操作
1.基本操作
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException
{
String driverClass="";
String url="";
String user="";
String password="";
Properties properties=new Properties();
InputStream inputStream=JDBCTest.class.getClassLoader().getResourceAsStream("dbcfg.properties");
properties.load(inputStream);
driverClass=properties.getProperty("driverClass");
url=properties.getProperty("url");
user=properties.getProperty("user");
password=properties.getProperty("password");
//System.err.println("驱动是："+driverClass+",\nurl是："+url+"\n用户名是："+user+"\n密码是："+password);
//1.注册驱动，共有三种方式
//1.1 注册驱动的第一种方式
/*
Driver driver=new Driver();
DriverManager.registerDriver(driver);
*/
//1.2注册驱动的第二种方式
/*
Driver driver=(Driver)Class.forName("com.mysql.jdbc.Driver").newInstance();
DriverManager.registerDriver(driver);
*/
//1.3 注册驱动的第三种方式
Class.forName(driverClass); //自动进行注册
//2.创建一个连接对象，共有三种方式
//2.1 使用DriverManager.getConnection(String url)创建连接对象
/*
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/library_db?user=root&password=123456");
*/
//2.2 使用DriverManager.getConnection(url, info)创建连对象
/*Properties properties=new Properties();
InputStream inputStream=JDBCTest.class.getClassLoader().getResourceAsStream("dbcfg.properties");
properties.load(inputStream);
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/library_db", properties);*/
//2.3 使用DriverManager.getConnection(url,user,password)
Connection conn=DriverManager.getConnection(url,user,password);
//3.创建一个SQL语句的发送命令对象
Statement stmt=conn.createStatement();
//4.1 执行SQL语句，拿到查询到的结果集对象
ResultSet rs=stmt.executeQuery("select * from student");
//4.2 执行插入命令
/*String insertSql="insert into dept(dept_id,coll_id,dept_name)values('0301','01','计算机科学导论')";
int n=stmt.executeUpdate(insertSql);
System.out.println(n);*/
//4.3 执行更新命令
/*String updateSql="update dept set dept_name='Computer Science Instriction' where dept_id='0301' ";
int n=stmt.executeUpdate(updateSql);
System.out.println(n);*/
//4.4 执行删除命令
/*String deleteSql="delete from dept where dept_id='0301' ";
int n=stmt.executeUpdate(deleteSql);
System.out.print(n);*/
//5.输出结果集的数据
while(rs.next())
{
//拿到结果集的方式
//1.根据字段名拿取（推荐）
/*String stuName=rs.getString("stu_name"); //代表的是结果集字段名
String stuId=rs.getString("stu_id");
System.out.println("学号为："+stuId+"的学生姓名是："+stuName);*/
//2.根据字段的索引（从1开始）
String stuName=rs.getString(2); //代表的是结果集字段名
String stuId=rs.getString(1);
System.out.println("学号为："+stuId+"的学生姓名是："+stuName);
}
//6.关闭连接、命令对象、结果集
//rs.close();
stmt.close();
conn.close();
}
}
2.标准写法：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
public class JdbcUtils {
private static final String DRIVER_CLASS;
private static final String URL;
private static final String USER;
private static final String PASSWORD;
static{
ResourceBundle rb=ResourceBundle.getBundle("dbcfg");
DRIVER_CLASS=rb.getString("driverClass");
URL=rb.getString("url");
USER=rb.getString("user");
PASSWORD=rb.getString("password");
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection()
{
try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void release(ResultSet rs,Statement stmt,Connection conn)
{
if(rs!=null)
{
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt!=null)
{
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3.通用的连接数据库的类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
public class JdbcUtils {
private static String driverClass = "" ;
private static String url = "" ;
private static String user = "" ;
private static String password = "";
static{
ResourceBundle rb = ResourceBundle.getBundle("dbcfg") ;
driverClass = rb.getString("driverClass") ;
url = rb.getString("url") ;
user = rb.getString("user") ;
password = rb.getString("password") ;
try {
Class.forName(driverClass) ;
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password) ;
} catch (SQLException e) {
e.printStackTrace();
}
return null ;
}
public static void release(ResultSet rs ,Statement stmt,Connection conn){
if(rs != null){
try {
rs.close() ;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close() ;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn != null){
try {
conn.close() ;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
4.批处理
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import org.junit.Test;
public class BatchTest {
@Test
public void testSatement() throws SQLException
{
//获得连接对象
Connection conn=JdbcUtils.getConnection();
//获得预处理对象
Statement stmt=conn.createStatement();
//创建批处理sql语句
String sqlStr1="insert into userTest values('100','100','100')";
String sqlStr2="insert into userTest values('200','200','200')";
String sqlStr3="insert into userTest values('300','300','300')";
//将批处理sql语句发送到statement对象中
stmt.addBatch(sqlStr1);
stmt.addBatch(sqlStr2);
stmt.addBatch(sqlStr3);
//执行sql语句
stmt.executeBatch();
//释放资源
JdbcUtils.release(null, stmt, conn);
}
@Test
public void testPreparedStatement() throws SQLException
{
//建立连接
Connection conn=JdbcUtils.getConnection();
//创建预处理对象
PreparedStatement pstmt=conn.prepareStatement("insert into userTest values(?,?,?)");
//指定？的值
pstmt.setInt(1, 400);
pstmt.setString(2, "400");
pstmt.setString(3, "400");
pstmt.addBatch();
pstmt.setInt(1, 500);
pstmt.setString(2, "500");
pstmt.setString(3, "500");
pstmt.addBatch();
pstmt.setInt(1, 600);
pstmt.setString(2, "600");
pstmt.setString(3, "600");
pstmt.addBatch();
//执行sql语句
pstmt.executeBatch();
//释放资源
JdbcUtils.release(null, pstmt, conn);
}
@Test
public void testInsertMore() throws SQLException
{
//建立连接
Connection conn=JdbcUtils.getConnection();
System.out.println(new Date());
//创建预处理对象
PreparedStatement pstmt=conn.prepareStatement("insert into userTest values(?,?,?)");
//指定？的值
for(int i=0;i<1000;i++)
{
pstmt.setInt(1, (i+1)*100+600);
pstmt.setString(2, "杨过");
pstmt.setString(3, "123");
pstmt.addBatch();
if(i%200==0)
{
pstmt.executeBatch();
//一定要清空缓存
pstmt.clearBatch();
}
}
System.out.println(new Date());
//释放资源
JdbcUtils.release(null, pstmt, conn);
}
}
5.获取数据库自动生成的主键
Connection conn = JdbcUtil.getConnection();
String sql = "insert into user(name,password) values('abc','123')";
PreparedStatement pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS );
pstmt.executeUpdate();
ResultSet rs = st.getGeneratedKeys(); //得到插入行的主键
if(rs.next())
System.out.println(rs.getObject(1));
注：只对insert有效。
6.常见数据库URL地址写法：
[Oracle] jdbc:oracle:thin:@localhost:1521:sid
[SqlServer] jdbc:microsoft:sqlserver://localhost:1433;DataBaseName=sid[mysql] jdbc:mysql://localhost:3306/sid(简写形式：jdbc:mysql:///sid)
常用属性：useUnicode=true&characterEncoding=UTF-8
7.Connnection对象常用方法：
i: createStatement()：创建向数据库发送sql的statement对象。
ii:prepareStatement(sql):创建向数据库发送预编译sql的PrepareStatement对象
iii:prpareCall(sql):创建执行存储过程的callableStatement对象
iv：setAutoCommit(boolean autoCommit):设置事务是否自动提交
v:commit():提交事务
vi:rollback():回滚事务
8.Statement对象常用方法：
i:executeQuery(String sql):用于向数据库发送查询语句
ii:executeUpdate(String sql):用于向数据库发送insert 、update、delete语句。
iii:execute(String sql):用于向数据库发送任意sql语句。
iv:addBatch(String sql)：把多条SQL语句放到一个批处理中
v:executeBatch():向数据库发送一批sql语句执行。
9.ResultSet对象常用方法：
i:获取任意类型的数据： getObject(int index); getObject(String columnName)
ii:获取指定类型的数据：getString(int index); getString(String columnName)
iii:next():移动到下一行
iv:previous():移动到前一行
v:absolute(int row):移动到指定行
vi:beforeFirst():移动到resultSet的最前面
vii:afterLast():移动到resultSet的最后面
浙公网安备 33010602011771号