JDBC连接数据库 登录 添加 查询 批量执行sql语句 游标类型 返回多行数据 大数据

于 2019-07-20 21:22:16 发布
阅读量795 收藏
点赞数 1
分类专栏： oracle笔记 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nutnutnutnutnut/article/details/96633228
版权

JDBC

Java数据库连接技术

java提供的一组连接各种数据库的通用api（接口）具体实现由个数据库的厂商来实现的驱动jar包就是实现jdbcapi的一组java类

eclipse连接Oracle

mysql-connector-java-8.0.16

java Interface Connection 静态sql语句,数据库连接

         Interface Statement   执行sql语句

       Interface 。。。Satement  执行预约sql 语句

        Intarface CallableStatement  执行存储过程

Interface ResultSet 相当于游标，读取查询结果

类：Class Drivermanager 管理驱动

启动服务

oracle的驱动——oracle安装目录——JDBC——lib——ojdbc6——复制

eclipse——建文件lib——build path

oracleDriver——驱动包

oracle服务oracleserviceORCL

public class ConnectToOracle{
  public static void main(String[] args){
    OracleDriver driver=new OracleDriver();
    //url数据库的ip + 端口  有效的账户
    //oracle url:jdbc:oracle:thin:@//<host>:<port>/ServiceName
    Properties pro=new Properties();
    pro.put("user","scott");
    pro.put("password","123");
    Connection conn=driver.connect("jdbc:oracle:thin@//localhost:1521/ORCL",pro);
    System.out.println(conn);
  }
}

eclipse连接Mysql

mysql-connector-java-8.0.16.jar

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.cj.jdbc.Driver");
		//jdbc:mysql://<host>:<port>/<database_name>?property1=value1&property2=value2
		String url="jdbc:mysql://localhost:3306/bbs?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Hongkong";
		String user="root";
		String password="123456";
		Connection conn=DriverManager.getConnection(url,user,password);
		System.out.println(conn);
	}

添加数据

public static void main(String[] args) throws SQLException, ClassNotFoundException {
		/*第一种连接方式
		OracleDriver driver=new OracleDriver();
		Properties pro=new Properties();
		pro.put("user", "scott");
		pro.put("password", "123");
		Connection conn=driver.connect("jdbc:oracle:thin:@//localhost:1521/ORCL", pro);
		System.out.println(conn);*/
		
		//第二种连接方式
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		System.out.println(conn);
		
		//执行sql命令获取执行结果
		//添加一个员工
		String sql="insert into emp values(9999,'xuchang','CLERK',7782,SYSDATE,3000,NULL,10)";
		Statement smt=conn.createStatement();
		/**
		 * smt.execute();  bool true表示有查询结果（哪怕查询结果没有数据），false表示无查询结果
		 * smt.executeUpdate(); int 表示受影响的行数，专用于执行insert delete update 语句
		 * smt.executeQuery(); ResultSet 执行select 语句
		 * smt.executeBatch(); int[] 批量执行多条sql语句
		 */
		int ret=smt.executeUpdate(sql);
		if(ret>0) {
			System.out.println("添加成功");	
		}
		smt.close();
		conn.close();
		//执行完释放资源
	}

查询数据

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		//查询某个职位的所有员工的工号，姓名，职位，薪水，入职日期
		Scanner input=new Scanner(System.in);
		String sql="select empno,ename,job,sal,hiredate from emp where job='"+input.next()+"' order by sal desc";
		Statement smt=conn.createStatement();
		ResultSet rs = smt.executeQuery(sql);
		//逐行提取数据
		//rs.next();//1.判断这行有没有数据 2.自动读完一行移动到下一行
		while(rs.next()) {
			int empno = rs.getInt("empno");
			String ename = rs.getString("ename");
			String job = rs.getString("job");
			Date hiredate = rs.getDate("hiredate");
			double sal=rs.getDouble("sal");
			System.out.println(empno+"\t"+ename+"\t"+job+"\t"+hiredate+"\t"+sal);
		}
		rs.close();
		smt.close();
		conn.close();
	}

登录

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		Scanner input=new Scanner(System.in);
		System.out.println("请输入用户名:");
		String username=input.nextLine();
		System.out.println("请输入密码：");
		String pwd=input.nextLine();
		
		String sql="select * from login where username='"+username+"' and pasd='"+pwd+"'";
		Statement smt=conn.createStatement();
		ResultSet rs=smt.executeQuery(sql);
		boolean flag=rs.next();
		rs.close();
		smt.close();
		conn.close();
		if(flag) {
			System.out.println("登录成功");	
		}
		else {
			System.out.println("用户名或密码错误");
		}		
	}

此方法不安全

方法2：

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		Scanner input=new Scanner(System.in);
		System.out.println("请输入用户名:");
		String username=input.nextLine();
		System.out.println("请输入密码：");
		String pwd=input.nextLine();
		
		String sql="select * from login where username=? and pasd=?";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, username);
		ps.setString(2, pwd);
		ResultSet rs=ps.executeQuery();
		boolean flag=rs.next();
		rs.close();
		ps.close();
		conn.close();
		if(flag) {
			System.out.println("登录成功");	
		}
		else {
			System.out.println("用户名或密码错误");
		}	
	}

批量执行

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//给三个员工加工资，每人100
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		String sql="update emp set sal=sal+100 where empno=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1,7788);
		ps.addBatch();
		ps.setInt(1, 7369);
		ps.addBatch();
		ps.setInt(1, 9999);
		ps.addBatch();
		
		ps.executeBatch();
		System.out.println("修改成功");
		ps.close();
		conn.close();
	}

练习

需求

用户登录——成功——进入菜单

——添加学生（学号（序列，从10000开始（无需输入）） 姓名，性别，出生日期，电话地址）——添加成功，请牢记你的学号 10001

——删除学生——输入学号——提示”你确定要删除学号为10001，姓名是张山 的学生吗“——<yes/no>

——修改学生的电话——输入学号——输入新的电话——直接修改

——查询——分组查询——查询男女学生各多少人

——输入每页的行数——输入当前页码——输出本页的数据

create table student(
stuno number(10) not null,
stuname varchar(10) not null,
sex char(2) not null,
borndate date,
phone varchar(20));

alter table student
add constraint pk_stuno primary key(stuno);

alter table student 
add constraint ck_sex check(sex='男' or sex='女');

create sequence sql_student_stuno
increment by 1
start with 10000;

insert into student values(sql_student_stuno.nextval,'里斯','男',sysdate,'12345678903');
insert into student values(10001,'李四','男',sysdate,'12345678902');

输入时间

ps.setTimestamp(4,new Timestemp(borndate.getTime()));//时间戳

                            第几个问号（输入的第几列）

to_char(borndate,‘yyyy-mm-dd hh24-mi-ss’)

数据库存储过程

输入型用in，输出型用out，输入输出型用 in out

create or replace procedure p_getsal
(
  v_deptno in number,--//执行需要的参数
  v_maxsal out number,--//保存执行的结果
  v_minsal out number
  
)
is
begin
select max(sal),min(sal) into v_maxsal,v_minsal from emp where deptno=v_deptno;
end;

返回单行数据

public static void main(String[] args) throws Exception{	
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
			String user="scott";
			String password="123";
			Connection conn=DriverManager.getConnection(url,user,password);
			
			CallableStatement call=conn.prepareCall("{call p_getsal(?,?,?)}");
			                                              //数据库中建好的过程(过程中参数)
			//输入型in setXXX()赋值即可
			call.setInt(1, 10);
			//输出型out 先注册
			call.registerOutParameter(2, java.sql.Types.NUMERIC);
			call.registerOutParameter(3, java.sql.Types.NUMERIC);
			//执行
			call.execute(); 
			//拿到输出参数的值
			double maxsal = call.getDouble(2);
			double minsal = call.getDouble(3);
			System.out.println("部门中工资最高的的是："+maxsal);
			System.out.println("部门中工资最低的的是："+minsal);
			call.close();
			conn.close();				
	}

返回多行数据

游标类型

 create or replace procedure p_getemp
(
v_jijie in varchar2,
v_result out sys_refcursor
)
is
begin
  open v_result for select ename,job,dname from emp
  inner join dept on dept.deptno=emp.deptno where hiredate in(
  select hiredate from emp where decode(to_char(hiredate,'q'),1,'春',2,'夏',3,'秋',4,'冬')=v_jijie);
  -----------------------------------to_char(hiredate,'q')=instr('春夏秋冬','春');
end;



public static void main(String[] args) throws Exception{	
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		CallableStatement call=conn.prepareCall("{call p_getemp(?,?)}");
		call.setString(1, "春");
		call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		call.execute();
		ResultSet rs=(ResultSet)call.getObject(2);
         //用游标类型接收数据，强转为游标类型
				while(rs.next()) {				System.out.println(rs.getString("ename")+"\t"+rs.getString("dname")+"\t"+rs.getString("job"));
				}
		rs.close();
		call.close();
		conn.close();
	}

大数据

存入数据

public static void main(String[] args) throws Exception{	
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		String sql="insert into images values(?,?,?)";
		PreparedStatement ps=conn.prepareStatement(sql);
  //PreparedStatement  表示预编译的sql对象。  sql语句编译并存储在PreparedStatement对象中，可使用该对象多次有效的执行此语句
		ps.setInt(1, 1001);
		//创建一个流
		InputStream in=new FileInputStream("F:\\wode\\image1\\1.jpg");
         //如果文件夹拒绝访问，查看路径之后有没有写图片名
		ps.setBlob(2, in);
		ps.setString(3, "1.jpg");
		int rows=ps.executeUpdate();
		ps.close();
		conn.close();
		if(rows>0) {
			System.out.println("添加成功");
		}
	}

读取数据

public static void main(String[] args) throws Exception{	
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		String sql="select * from images where imgid=1001";
		PreparedStatement ps=conn.prepareStatement(sql);
		ResultSet rs=ps.executeQuery();
         //数据库结果集的数据表，next方法可将光标移动到下一行，没有对象时返回false
		if(rs.next()) {
			int imgid=rs.getInt("imgid");
			String imagename=rs.getString("imgname");
			Blob blob=rs.getBlob("image");
			//创建流
			InputStream in=blob.getBinaryStream();
             //从数据库中读取文件
			OutputStream out=new FileOutputStream("F:\\wode\\image2\\"+imagename);
             //输出到文件夹
			byte[] bt=new byte[1024];
			int len=0;
			while((len=in.read(bt))!=-1) {
				out.write(bt,0,len);
			}		
			in.close();
			out.close();
			System.out.println("文件编号："+imgid+"文件名："+imagename);
		}
		rs.close();
		conn.close();
		ps.close();
		
	}

存取数据

创建集合，序列化，存入数据库，从数据库中取出

import java.io.Serializable;
import java.util.Date;

public class Student implements Serializable{
	private int stuno;
	private String stuname;
	private String sex;
	private Date borndate;
	private String phone;
	public int getStuno() {
		return stuno;
	}
	public void setStuno(int stuno) {
		this.stuno = stuno;
	}
	public String getStuname() {
		return stuname;
	}
	public void setStuname(String stuname) {
		this.stuname = stuname;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBorndate() {
		return borndate;
	}
	public void setBorndate(Date borndate) {
		this.borndate = borndate;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	@Override
	public String toString() {
		return "Student [stuno=" + stuno + ", stuname=" + stuname + ", sex=" + sex + ", borndate=" + borndate
				+ ", phone=" + phone + "]";
	}
	public Student(int stuno, String stuname, String sex, Date borndate, String phone) {
		super();
		this.stuno = stuno;
		this.stuname = stuname;
		this.sex = sex;
		this.borndate = borndate;
		this.phone = phone;
	}
	public Student() {
		super();
	}
}



import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class Test {
	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, ParseException {
		List<Student> list=new ArrayList<Student>();
		//集合当作对象
		DateFormat df=new SimpleDateFormat("yyyy-mm-dd");
		/**
		 * create table tbobjects(oid number(10) not null,object blob not null);
		 */
		list.add(new Student(10018,"赵武","男",df.parse("2019-12-11"),"12390873874"));
		list.add(new Student(10011,"赵子龙","男",df.parse("1724-12-11"),"12390873879"));
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		String sql="insert into tbobjects values(?,?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, 100);
		
		ByteArrayOutputStream bo=new ByteArrayOutputStream();//将数据写入字节数组
		ObjectOutputStream out=new ObjectOutputStream(bo);//将java对象原始数据类型写入outputstream(输出流，接收输出字节并将其发送到某个接收器)
		out.writeObject(list);//将对象序列化到内存流,转换为字节数组
		bo.close();
		out.close();
		byte [] bt=bo.toByteArray();//将内存流用字节数组接收
		InputStream in=new ByteArrayInputStream(bt);//读取数组中数据
		ps.setBlob(2, in);//二进制  object类型
		
		int rows=ps.executeUpdate();
		ps.close();
		conn.close();
		if(rows>0) {
			System.out.println("保存成功");
		}
	}
}



import java.io.InputStream;
import java.io.ObjectInputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class ReadeList {
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url="jdbc:oracle:thin:@//localhost:1521/ORCL";
		String user="scott";
		String password="123";
		Connection conn=DriverManager.getConnection(url,user,password);
		
		String sql="select * from tbobjects where oid=?";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, 100);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			int oid=rs.getInt("oid");
			System.out.println("对象编号"+oid);
			Blob blob=rs.getBlob("object");
			InputStream in=blob.getBinaryStream();//将对象转化为流类型，拿到流
			ObjectInputStream oin=new ObjectInputStream(in);//读取流，反序列化流
			List<Student> list=(List<Student>)oin.readObject();//将读取的数据放入集合，转化为字节对象
			oin.close();
			for(Student stu: list) {
				System.out.println(stu);
			}
		}
		rs.close();
		ps.close();
		conn.close();
	}
}
又一年冬天
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值