JDBC笔记

MYSQL常用语句：

//创建表格
CREATE TABLE student (
id INT(20) COMMENT ‘学号’,
studentname VARCHAR(20) COMMENT ‘姓名’,
score FLOAT DEFAULT 0 COMMENT ‘成绩’
)
CREATE TABLE parent (
studentname VARCHAR(20) COMMENT ‘姓名’,
mon VARCHAR(20) COMMENT ‘母亲’,
father VARCHAR(20) COMMENT ‘父亲’
)

//修改表格操作
ALTER TABLE student ADD age VARCHAR(20); //添加属性
ALTER TABLE student MODIFY age INT(20); //修改属性
ALTER TABLE student DROP age; //删除属性
ALTER TABLE student CHANGE age Age INT(20); //修改属性名

//添加数据操作
INSERT INTO student VALUES (21,‘嘉豪’,90)
INSERT INTO student SET id = 22
INSERT INTO student VALUES (23,‘侯哥’,70),(24,‘吉吉’,89)
INSERT INTO parent VALUES (‘吉吉’,‘测试’,‘而非’),(‘嘉豪’,‘读法’,‘速度’)

//删除数据操作
DELETE FROM student WHERE id = 21; //条件必须加，否则数据会被全部删除

//更新数据操作
UPDATE student SET id = 30 WHERE score = 70;

//查询数据操作
SELECT * FROM student; //查询全部
SELECT id FROM student; //查询单一字段数据
SELECT id FROM student WHERE id>25; //查询单一字段数据约束条件
SELECT score FROM student ORDER BY score ASC; //按照字段的降序输出
SELECT * FROM student ORDER BY score DESC; //按照字段的升序输出

//聚合函数操作
SELECT MAX(score)FROM student;

//表的连接
//左外连接
SELECT * FROM student LEFT JOIN parent ON student.studentname= parent.studentname;

JDBC连接

驱动类导入：
下载jdbc-connection.jar类包，然后在eclipese里面build path导入类包
注意mysql和jdbc包的版本要对应，版本不对的话会报错
加载驱动代码：

//加载驱动类
			//反射导入的类包
			Class.forName("com.mysql.cj.jdbc.Driver");
			//连接对象其实是一个scoket对象，是一个比较远程的连接，比较耗时）
			//真正开发要用连接池，提高效率
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名?serverTimezone=Hongkong","数据库账号","数据库密码");
			System.out.println(conn); // 连接成功

JDBC常用接口

Statement接口

用来执行SQL语句并返回结果的对象

			//如何使用statement
			s = conn.createStatement();  //创建statement接口
			String sql = "insert into student values (21,'你好',89)";
			s.execute(sql);  //传入sql语句执行
			
			//statement不安全，拼字符串任意被恶意修改数据
			String id = "5 or 1=1 ";
			String sql1 = "delete from text where id="+id;
//			s.execute(sql1);   如果执行的话，会清空数据库	

PreparedStatement接口

Statement接口不安全，可能会被输入恶意的字符串，而这个结构就比statement安全，它是Statement接口的子类，可以使用Statement的方法，同时新方法是可以先对sql需要输入的值设置？占位符，然后在后面set方法加入数据，实现安全的操作。

			String sql = "insert into student (id,studentname) values (?,?)"; //？占位符
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, 23);   //从1开始算的，第一个？
			ps.setString(2, "小胖");  //第2个占位符
			
			//不管类型也可以，setObject
			ps.setObject(1,42);
			ps.setObject(2,"菜单");

			System.out.println("插入记录");
//			ps.execute();
			int count  = ps.executeUpdate();  //记录更新了多少条记录，适用于insert/delete的语句
			System.out.println(count);	

resultSet接口

Statement和preparement都可以通过输入sql语句改变数据库内容，但如果要select获取到数据库数据，就需要增加resultSet来获取信息。

ResultSet rs = ps.executeQuery();  //也是一个接口，返回结果集，适用于查询语句
			
			while(rs.next()) {
				System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"---"+rs.getInt(3));
			}

JDBC批处理

addBatch

	Statement st = conn.createStatement();
			
			long start = System.currentTimeMillis();
			for(int i=0;i<2000;i++) {
				st.addBatch("insert into text (oo,cdcd) values ("+i+",'佳楷')");
			}
			
			st.executeBatch();

事务

一组要么同时执行成功，要么同时执行失败的SQL语句，是数据库的一个执行单元。
通常开始于DML语句。
结束于:
1执行COMMIT或ROLLBACK语句
2执行DDL或DCL语句，例如CREATE TABLE，GRANT语句，会自动执行COMMIT语句
3断开数据库连接

四大特点：ACID
原子性：要么同时执行，要么同时失败
一致性：有操作失败时，所有更改过的数据要回滚到修改之前
隔离性：两个事务同时操作的时候，只能查看到另一事务修改之前，或者修改之后的状态，不能查看在操作时的数据。
持久性：修改后是永久保存的

			conn.setAutoCommit(false);   //JDBC默认自动提交事务true
			
			String sql = "insert into student (id,studentname,score) values(?,?,?)";
			PreparedStatement ps1 = conn.prepareStatement(sql);
			ps1.setObject(1, 56);  
			ps1.setObject(2,"搞其");
			ps1.setObject(3, 78);
			ps1.execute();
			System.out.println("插入一个用户");
			
			try {
				Thread.sleep(6000);
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			第二个语句，如果insert语句出错，则会回滚到事务执行之前，也就是上面的语句会消失，整个事务不执行，数据库不会改变
			String sql1 = "insert into student (id,studentname,score) values(?,?)";
			PreparedStatement ps2 = conn.prepareStatement(sql1);
			ps2.setObject(1, 65);   
			ps2.setObject(2,"册封");
			ps2.setObject(3, 65);
			ps2.execute();
			System.out.println("插入第二个用户");
			
			conn.commit();  //提交事务

要实现事务就要先 conn.setAutoCommit(false)； 把自动提交改为手动，在下面写入操作，最后commit（）；如果之间有错误的，则全部会返回原来的样子，把已经改变的复原回去

时间处理

sql的时间数据类型不是java的util包内的，是java.sql内的，注意不要导错包

• date 表示年月日
• Time 表示时分秒
• TimeStamp 表示年月日时分秒

//插入时间对象要用java.sql.date包
			int rand = 1000000+new Random().nextInt(100000);
			Date date = new Date(System.currentTimeMillis()-rand);  //年月日
			Timestamp ts = new Timestamp(System.currentTimeMillis()-rand); //年月日时分秒
			
			ps1.setObject(3, date);
			ps1.setObject(4, ts);

写入具体的日期字符串转成日期类型也可以导入

	//将字符串代表的日期转为long类型
	public static  long str2Date(String date) {
		DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			return format.parse(date).getTime();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return  0;
		}
	}

查找对应时间段内的数据对象

ps = conn.prepareStatement("select * from student where lasttime>? and lasttime<? order by lasttime ");
			
			Timestamp start = new Timestamp(str2Date("2021-4-6 7:52:24"));
			Timestamp end = new Timestamp(str2Date("2021-4-6 7:52:30"));

			ps.setObject(1, start);
			ps.setObject(2, end);
			
			rs = ps.executeQuery();
			
			while(rs.next()) { //输出满足条件的区间
				System.out.println(rs.getInt(1)+"-->"+rs.getString(2)+"-->"+rs.getTimestamp(5));
			}

CLOB 大文本操作

输入读取都是通过文件流操作

ps = conn.prepareStatement("insert into student (studentname,myInfo) values (?,?)");
			ps.setObject(1, "高淇");
			//输入
			ps.setClob(2,new FileReader(new File("d:/a.txt")));
			//不能直接写字符串，要通过流直接输入text的内容，需要的是文件流，所有如果输入字符，要先InputStreamReader转型
			
			
			
			//读取
			ps = conn.prepareStatement("select * from student where id = 300");
			rs = ps.executeQuery();
			while(rs.next()) {
				Clob  c = rs.getClob("myInfo");
				Reader r = c.getCharacterStream();
				int temp = 0;
				while((temp=r.read())!=-1){
					System.out.print((char)temp);
				}
			}

BLOB 大量的二进制数据

二进制流，直接FileInputStream

ps = conn.prepareStatement("insert into student (studentname,image) values (?,?)");
			ps.setObject(1, "高淇");
			ps.setBlob(2, new FileInputStream("d:/java/e.png"));
			ps.execute();
			
			//读取数据
			ps = conn.prepareStatement("select * from student where id = 301");
			rs = ps.executeQuery();
			while(rs.next()) {
				Blob  c = rs.getBlob("image");
				InputStream  is = c.getBinaryStream();
				FileOutputStream fos = new FileOutputStream("d:/copy.jpg");
				int len = 0;
				while((len=is.read())!=-1){
					fos.write(len);
				}
			}

封装JDBC连接断开操作

设置一个properties资源文件，url，driver，root，pwd设置好

//封装工具类简化使用JDBC
public class JDBCUtil {

	//读取资源文件 properties
	static Properties pros = null;  //可以帮助我们读取和处理资源文件的信息
	static {
		pros = new Properties();
		
		try {
			pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getMysqlConn() {  //获取connection
		try {
			
			Class.forName(pros.getProperty("mysqlDriver"));
			return  DriverManager.getConnection(pros.getProperty("mysqlURL"),
					pros.getProperty("mysqlUser"),pros.getProperty("mysqlPwd"));
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//关闭的封装类
	public static void close(ResultSet rs,Statement ps,Connection conn) {
		
			try {
				if(rs!=null)
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if(ps!=null)
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if(conn!=null)
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	public static void close(Statement ps,Connection conn) {
		
		try {
			if(ps!=null)
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if(conn!=null)
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
}
public static void close(Connection conn) {
		
		try {
			if(conn!=null)
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
}
}