2020-10-14JDBC知识点

JDBC初始+UPDATE操作

1.JDBC操作数据库的步骤

1.1.导入JDBC驱动

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency><dependency>
	        <groupId>mysql</groupId>
	        <artifactId>mysql-connector-java</artifactId>
	        <version>8.0.21</version>
    	</dependency>

1.2.加载驱动类

版本是8.x的时候 “com.mysql.cj.jdbc.Driver”
版本是5.x的时候 “com.mysql.jdbc.Driver”
			String className = "com.mysql.jdbc.Driver";
            Class.forName(className);

1.3.获取连接

版本是8.x的时候 “jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST”
版本是5.x的时候 “jdbc:mysql://localhost:3306/newdb3”
			String url = "jdbc:mysql://localhost:3306/newdb3";
            String user = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url,user,password);

1.4.创建状态参数

			Statement stat = conn.createStatement();

1.5.执行数据库操作

			String sql = "INSERT INTO  EMP(EMPNO,ENAME) VALUES('111','张三') ";
            //增删改"executeUpdate(sql)"
            //查询"executeQuery(sql)"
            stat.executeUpdate(sql);

1.6.关闭连接

			stat.close();
            conn.close();

2.JDBC的一些小细节

2.1.版本不同时"加载驱动类"和"获取连接"不同的问题

2.2.加载驱动类的问题

第一种执行方式

			String className = "com.mysql.jdbc.Driver";
            Class.forName(className);
加载类就是执行这个类的静态块,静态块的代码如下
		static {
	        try {
	            java.sql.DriverManager.registerDriver(new Driver());
	        } catch (SQLException E) {
	            throw new RuntimeException("Can't register driver!");
	        }
	    }

所以可以直接执行静态块里代码

第二种执行方式

		java.sql.DriverManager.registerDriver(new Driver());
Driver类下的构造方法是空的,静态块代码如下
		static {
	        try {
	            java.sql.DriverManager.registerDriver(new Driver());
	        } catch (SQLException E) {
	            throw new RuntimeException("Can't register driver!");
	        }
	    }

所以可以直接创建Driver对象

第三种执行方式

		new Driver()

第四种执行方式,通过系统直接设置属性值完成

		System.setProperty("jdbc.Driver","com.mysql.cj.jdbc.Driver");

第五种执行方式,什么都不写

DriverManager类下有一个静态块,静态块里执行了这个方法"loadInitialDrivers()",方法里会找寻串并加载驱动类

2.3.事务的操作

默认为自动提交事务,参数是true为自动提交,数为false为手动提交

		conn.setAutoCommit(boolean autoCommit);

2.4.事务的隔离级别

		conn.setTransactionIsolation(int level);

2.5.事务的提交/回滚/保存还原点/跳转至还原点

			conn.commit();
            conn.setSavepoint();
            conn.rollback();
            Savepoint savepoint = conn.setSavepoint(String name);
            conn.rollback(Savepoint savepoint);	

3.JDBC封装

3.1.JDBC查询单条记录的封装

		public Atm selecttOne(String name) {
	        Atm atm = null;
	        Connection conn = null;
	        Statement stat = null;
	        ResultSet rs = null;
	        try {
	            Class.forName("com.mysql.cj.jdbc.Driver");
	            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=CST", "root", "root");
	            stat = conn.createStatement();
	            rs = stat.executeQuery("SELECT * FROM ATM WHERE NAME='" + name + "'");
	            if (rs.next()) {
	                atm = new Atm(rs.getString("name"), rs.getString("pass"), rs.getFloat("money"));
	            }
	        } catch (Exception e) {
	            e.printStackTrace();
	        } finally {
	            if (rs != null) {
	                try {
	                    rs.close();
	                } catch (SQLException throwables) {
	                    throwables.printStackTrace();
	                }
	            }
	            if (stat != null) {
	                try {
	                    stat.close();
	                } catch (SQLException throwables) {
	                    throwables.printStackTrace();
	                }
	            }
	            if (conn != null) {
	                try {
	                    conn.close();
	                } catch (SQLException throwables) {
	                    throwables.printStackTrace();
	                }
	            }
	        }
	        return atm;
	    }

32.JDBC修改数据的封装

		public void update(Atm atm) {
	        Connection conn = null;
	        Statement stat = null;
	        ResultSet rs = null;
	        try {
	            Class.forName("com.mysql.cj.jdbc.Driver");
	            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/aotian?serverTimezone=CST", "root", "root");
	            stat = conn.createStatement();
	            String sql = "UPDATE ATM SET ANAME = '"+atm.getAname()+" ' ";
	            StringBuffer sb = new StringBuffer(sql);
	            if(atm.getApass()!=null && atm.getApass()!=""){
	                sb.append(", APASS = '"+atm.getApass()+"'");
	            }
	            if(atm.getAmoney()!=null){
	                sb.append(", AMONEY = "+atm.getAmoney()+" ");
	            }
	            sb.append("WHERE ANAME = '"+atm.getAname()+"'");
	            System.out.println(sb.toString());
	            int count = stat.executeUpdate(sb.toString());
	            if(count==0){
	                throw new RuntimeException("数据库执行时产生异常");
	            }
	        } catch (Exception e) {
	            e.printStackTrace();
	        } finally {
	            if (rs != null) {
	                try {
	                    rs.close();
	                } catch (SQLException throwables) {
	                    throwables.printStackTrace();
	                }
	            }
	            if (stat != null) {
	                try {
	                    stat.close();
	                } catch (SQLException throwables) {
	                    throwables.printStackTrace();
	                }
	            }
	            if (conn != null) {
	                try {
	                    conn.close();
	                } catch (SQLException throwables) {
	                    throwables.printStackTrace();
	                }
	            }
	        }
	    }

4.模糊查询

4.1.方式一

		public void testCase() {
	        try {
	            Class.forName("com.mysql.cj.jdbc.Driver");
	            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
	            PreparedStatement pstat = conn.prepareStatement("SELECT * FROM EMP WHERE ENAME LIKE ?");
	            pstat.setString(1,"%A%");
	            ResultSet set = pstat.executeQuery();
	            while (set.next()){
	                System.out.println(set.getInt("empno")+"-"+set.getString("ename"));
	            }
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	        //关闭。。。
	    }

4.2.方式二,更麻烦

		public void testCase() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
            PreparedStatement pstat = conn.prepareStatement("SELECT * FROM EMP WHERE ENAME LIKE \"%\"?\"%\"");
            pstat.setString(1,"A");
            ResultSet set = pstat.executeQuery();
            while (set.next()){
                System.out.println(set.getInt("empno")+"-"+set.getString("ename"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //关闭。。。
    }

5.分页查询

	//参数是页数和行数
    public void testCase(int m,int n) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
            PreparedStatement pstat1 = conn.prepareStatement("SELECT * FROM EMP ORDER BY EMPNO LIMIT ?,?");
            pstat1.setInt(1,5*(m-1));
            pstat1.setInt(2,n);
            ResultSet set1 = pstat1.executeQuery();
            while (set1.next()){
                System.out.println(set1.getInt("empno")+"-"+set1.getString("ename"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //关闭。。。
    }

6.联合查询

		public void testCase(int m,int n) {
		        try {
		            Class.forName("com.mysql.cj.jdbc.Driver");
		            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?serverTimezone=CST","root","root");
		            PreparedStatement pstat1 = conn.prepareStatement("SELECT EMP.*,DEPT.DNAME,DEPT.LOC FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO ORDER BY EMPNO LIMIT ?,?");
		            pstat1.setInt(1,5*(m-1));
		            pstat1.setInt(2,n);
		            ResultSet set1 = pstat1.executeQuery();
		            while (set1.next()){
		                Emp emp = new Emp(set1.getInt("empno"),set1.getString("ename"),set1.getString("job"),set1.getInt("mgr"),set1.getDate("hiredate"),
		                        set1.getDouble("sal"),set1.getDouble("comm"),new Dept(set1.getInt("deptno"),set1.getString("dname"),set1.getString("LOC")));
		                System.out.println(emp);
		            }
		        } catch (Exception e) {
		            e.printStackTrace();
		        }
		        //关闭。。。
		    }

7.联合查询

如何查询的内容不适合放在实体类中,可以放在集合中进行处理

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值