jdbc使用
1. 导包
直接使用idea导入依赖包即可
新建一个lib,把jar包放在这里
2. 加载驱动
class.forname("oracle.jdbc.driver.oracledriver");
3. 创建connection对象
连接数据库,从而获得connection对象
string url = "jdbc:oracle:thin:@localhost:1521:orcl";//具体的实例名可以查看自己的服务管理,有个 connection conn = drivermanager.getconnection(url,"scott","tiger");//后面的两个参数分别为连接数据库的用户名和密码
ps:查看实例名
- 开始->运行->输入
services.msc
- 找到服务
- 后面的就是我们的oracle数据库的实例名
4. 创建statement对象
statement stmt = conn.createstatement();
- statement: 普通语句类型,只能执行一条完整的语句
- preparedstatement: 预编译语句,它支持占位符“?”, 如:select * from emp where ename like ? and sal>?
- callablestatement: 支持存储过程的调用
preparedstatement
和callablestatement
都是继承statement
,比statement
要强大
补充链接(暂时未写):
preparedstatement的使用
callablestatement的使用
5. 执行sql语句
//执行查询语句,会返回有个结果集resultset resultset rs = stmt.executequery("select * from student");
6. 使用resultset获取数据库数据
//遍历结果集, 每次调用next方法时会使结集的游标后移,最初游标位于第一行之前 //student累是我们写的一个bean类,就是把数据库中的每一条数据当作为一个对象,每一列都当作为student类的属性 while (rs.next()){ //第循环一次封装一条记录 student student = new student(); student.setnum(rs.getint("num")); student.setname(rs.getstring("name")); //获取其它字段rs.getxxx("列名") xxx表示相应的数据类型 //或者也可以re.getxxx(int columnnum) 参数为第几列 //如果不知道列的具体类型,也可以使用rs.getobject }
oracle与java的类型对应
oracle | java |
---|---|
varchar/varchar(2) | string |
int | int |
number | double |
date | date |
7. 关闭
依次关闭resultset
statement
connection
补充
- 一般将连接方法和关闭方法封装为静态方法调用,连接方法和关闭方法封装在jdbcutil工具类中
- 开启数据库链接的三个参数一般我们不会更改,所以我们直接写死在资源文件里面
- 设置编码utf-8,使用中文字符就会自动转为unicode
- 一般写在与src同目录,代码如下
jdbc.url = jdbc:oracle:thin:@localhost:1521:orcl jdbc.driver = oracle.jdbc.driver.oracledriver jdbc.username = stars jdbc.password = stars
- 写一个dao类,其中有update,save,delete,find等方法,从而实现对数据库的增删改查
jdbcutil工具类:
package homework; import java.sql.connection; import java.sql.drivermanager; import java.sql.resultset; import java.sql.sqlexception; import java.sql.statement; import java.util.resourcebundle; /** * @author starsone * @date create in 2019/4/24 0024 22:18 * @description */ class jdbcutil { private static final string driver = getvalue("jdbc.driver"); private static final string url = getvalue("jdbc.url"); private static final string username = getvalue("jdbc.username"); private static final string password = getvalue("jdbc.password"); static{ try { //注册数据库驱动程序 class.forname(driver); } catch (classnotfoundexception e) { system.err.println("注册数据库驱动程序失败。" + e.getmessage()); } } /** * 获取数据库连接 * @return 一个connection */ public static connection getconnection() { try { connection conn = drivermanager.getconnection(url, username, password); return conn; } catch (sqlexception e) { system.err.println("a获得数据连接失败。" + e.getmessage()); } return null; } /** * 关闭 * @param conn * @param stmt * @param rs */ public static void close(connection conn, statement stmt, resultset rs) { try { //关闭数据库的资源的顺序最好与使用的顺序相反 if(rs != null){ rs.close(); } if(stmt != null){ stmt.close(); } if(conn != null){ conn.close(); } } catch (sqlexception e) { e.printstacktrace(); } } /** * 读取属性文件中的信息 * * @param key * @return */ private static string getvalue(string key) { // 资源包绑定 resourcebundle bundle = resourcebundle.getbundle("jdbc"); return bundle.getstring(key); } public static void main(string[] args) { system.out.println(getvalue("jdbc.driver")); system.out.println(getconnection()); } }
studentdao.java
我的代码还没有补全,使用的statement
语句
package homework; import java.sql.connection; import java.sql.date; import java.sql.resultset; import java.sql.sqlexception; import java.sql.statement; import java.util.arraylist; import java.util.list; /** * @author starsone * @date create in 2019/4/24 0024 22:24 * @description */ class studentdao { /** * 添加一个 * @param student */ public void save(student student) { string name = student.getname(); int num = student.getnum(); connection connection = null; statement statement = null; try { connection = jdbcutil.getconnection(); statement = connection.createstatement(); //拼接sql语句,把数据插入到数据库中 statement.execute("insert into student value(" + num + "," + name + ") "); } catch (sqlexception e) { e.printstacktrace(); } finally { jdbcutil.close(connection, statement, null); } } public void update(student student) { } /** * 删除指定编号的学生 * @param num */ public void delete(integer num) { connection connection = null; statement statement = null; try { connection = jdbcutil.getconnection(); statement = connection.createstatement(); //拼接sql语句,把数据插入到数据库中 boolean flag = statement.execute("delete from student where num = " + num); if (flag) { system.out.println("删除成功!"); } else { system.out.println("删除失败!"); } } catch (sqlexception e) { e.printstacktrace(); } finally { jdbcutil.close(connection, statement, null); } } /** * 主键查询 * @param number */ public void findbypk(integer number) { connection connection = null; statement statement = null; resultset resultset=null; try { connection = jdbcutil.getconnection(); statement = connection.createstatement(); //查询query,返回一个数据集 resultset = statement.executequery("select * from student where num =" + number); while (resultset.next()) { int num = resultset.getint("num"); string name = resultset.getstring("name"); string sex = resultset.getstring("sex"); int age = resultset.getint("age"); date birthdate = resultset.getdate("birthdate"); double grade = resultset.getdouble("grade"); //新建一个对象 new student(num, age, grade, name, sex, birthdate); } } catch (sqlexception e) { e.printstacktrace(); } finally { jdbcutil.close(connection, statement, resultset); } } /** * 查询全部 * @return */ public list<student> findall() { list<student> list = new arraylist<>(); connection connection = null; statement statement = null; resultset resultset=null; try { connection = jdbcutil.getconnection(); statement = connection.createstatement(); //查询query,返回一个数据集 resultset = statement.executequery("select * from student" ); while (resultset.next()) { int num = resultset.getint("num"); string name = resultset.getstring("name"); string sex = resultset.getstring("sex"); int age = resultset.getint("age"); date birthdate = resultset.getdate("birthdate"); double grade = resultset.getdouble("grade"); //新建一个对象 list.add(new student(num, age, grade, name, sex, birthdate)); } } catch (sqlexception e) { e.printstacktrace(); } finally { jdbcutil.close(connection, statement, resultset); } return list; } }