jdbc连接数据库
步骤:(增删改)
1.导jar包
2.加载驱动类,Class.forName("类名");
3.建立DriverManager连接,获得连接对象
4.通过Connection对象创建Statement,调用它的executeUpdate(sql),它可以发送DML、DDL
String url = "jdbc:mysql://localhost:3306/test";
Class.forName("com.mysql.jdbc.Driver");//加载驱动类
Connection con=DriverManager.getConnection(url,"root","123");//获得连接对象
步骤:(查询)
一、得到Connection
二、得到Statement,发送select语句
三、对查询返回的“表格”进行解析!
/*
* 一、得到连接
* 1. 准备四大连接参数
*/
String driverClassName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/exam";
String username = "root";
String password = "123";
/*
* 2. 加载驱动类
*/
Class.forName(driverClassName);
/*
* 3. 通过省下的三个参数调用DriverManger的getConnection(),得到连接
*/
Connection con = DriverManager.getConnection(url, username, password);
/*
* 二、得到Statement,执行select语句
* 1. 得到Statement对象:Connection的createStatement()方法
*/
Statement stmt = con.createStatement();
/*
* 2. 调用Statement的ResultSet rs = executeQuery(String querySql)
*/
ResultSet rs = stmt.executeQuery("select * from emp");
/*
* 三、解析ResultSet
* 1. 把行光标移动到第一行,可以调用next()方法完成!
*/
while(rs.next()) {//把光标向下移动一行,并判断下一行是否存在!
int empno = rs.getInt(1);//通过列编号来获取该列的值!
String ename = rs.getString("ename");//通过列名称来获取该列的值
double sal = rs.getDouble("sal");
System.out.println(empno + ", " + ename + ", " + sal);
}
/*
* 四、关闭资源
* 倒关
*/
rs.close();
stmt.close();
con.close();//这个东东,必须要关
关于ResultSet结构与行光标:
获取结果集元数据
l 得到元数据:rs.getMetaData(),返回值为ResultSetMetaData;(获得表结构)
l 获取结果集列数:int getColumnCount()
l 获取指定列的列名:String getColumnName(int colIndex)
当不知道表的列数时,可通过以下方法获得结果集:
Connection con = DriverManager.getConnection(url,"root","123");//获得连接对象 Statement stmt = (Statement) con.createStatement(); String sql = "select * from demo"; ResultSet rs = stmt.executeQuery(sql); /* while(rs.next()){ int id = rs.getInt(1); String name = rs.getString(2); int grade = rs.getInt(3); System.out.println(id+", "+name+", "+grade); } */ int count = rs.getMetaData().getColumnCount(); while(rs.next()){ for(int i=1;i<=count;i++){ System.out.print(rs.getString(i)); if(i<count){ System.out.print(","); } } System.out.println(); }
规范化:
1、加上try-catch-finally代码块
2、将三个引用放在try代码块之外(conn、stmt、rs)
3、先判断是否有空指针异常,再关闭资源
public void fun3() throws Exception { Connection con = null;//定义引用 Statement stmt = null; ResultSet rs = null; try { String driverClassName = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/exam"; String username = "root"; String password = "123"; Class.forName(driverClassName); con = DriverManager.getConnection(url, username, password);//实例化 stmt = con.createStatement(); String sql = "select * from emp"; rs = stmt.executeQuery(sql); rs.last();//把光标移动到最后一行 System.out.println(rs.getRow()); rs.beforeFirst(); // while(rs.next()) { // System.out.println(rs.getObject(1) + ", " // + rs.getString("ename") + ", " + rs.getDouble("sal")); // } int count = rs.getMetaData().getColumnCount(); while(rs.next()) {//遍历行 for(int i = 1; i <= count; i++) {//遍历列 System.out.print(rs.getString(i)); if(i < count) { System.out.print(", "); } } System.out.println(); } } catch(Exception e) { throw new RuntimeException(e); } finally { // 关闭 if(rs != null) rs.close(); if(stmt != null) stmt.close(); if(con != null) con.close(); } }