JDBC入门学习

喜欢ヅ旅行 2024-04-20 07:32 101阅读 0赞

⭐**简单说两句**⭐

> **作者：****后端小知识**
> 
> **CSDN**个人主页：[后端小知识][Link 1]
> 
> ?**GZH**：`后端小知识`
> 
> ?欢迎关注?点赞?收藏⭐️留言?

![image-20231006234204124][]

#### 文章目录 ####

*  JDBC
 *   *  JDBC访问数据库步骤
     *  模拟登录
     *  事务

## JDBC ##

> 内容和代码为在校期间做实验写的，初学者可以参考参考，其中也有很多写法不规范，但是入门还是可以，后面看情况可以出一篇详细的学习JDBC的文章

### JDBC访问数据库步骤 ###

• 1：加载一个Driver驱动

• 2：创建数据库连接（Connection）

• 3 ：创建SQL命令发送器Statement

• 4：通过Statement发送SQL命令并得到结果

• 5：处理结果（select语句）

• 6：关闭数据库资源

• ResultSet

• Statement

• Connection

public class TestInsert {
        
        public static void main(String[] args) throws Exception{
        
            String driver = "com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user="root";
            String pasaword="tcx119";
            //加载驱动
            Class.forName (driver);
            //和数据库建立连接
            Connection conn = DriverManager.getConnection (url,user,pasaword);
           // System.out.println (conn);
            //创建sql命令发射器
            Statement smt = conn.createStatement ();
            //准备sql命令
            String sql = "insert into score values(12345666,'X6666',90.2,85.3)";
            //处理结果
            int n = smt.executeUpdate (sql);
            if(n>0){
        
                System.out.println ("插入成功");
            }else{
        
                System.out.println ("插入失败");
            }
            //关闭资源
            smt.close ();
            conn.close ();
        }
    }

`查询的结果`

`code`

//这是Emp的代码
    public class Emp {
        
        private String teacherno;
        private String tname;
        private String major;
        private String prof;
        private String department;
    
        public Emp() {
        
        }
    
        public Emp(String teacherno, String tname, String major, String prof, String department) {
        
            this.teacherno = teacherno;
            this.tname = tname;
            this.major = major;
            this.prof = prof;
            this.department = department;
        }
    
        public String getTeacherno() {
        
            return teacherno;
        }
    
        public void setTeacherno(String teacherno) {
        
            this.teacherno = teacherno;
        }
    
        public String getTname() {
        
            return tname;
        }
    
        public void setTname(String tname) {
        
            this.tname = tname;
        }
    
        public String getMajor() {
        
            return major;
        }
    
        public void setMajor(String major) {
        
            this.major = major;
        }
    
        public String getProf() {
        
            return prof;
        }
    
        public void setProf(String prof) {
        
            this.prof = prof;
        }
    
        public String getDepartment() {
        
            return department;
        }
    
        public void setDepartment(String department) {
        
            this.department = department;
        }
    
        @Override
        public String toString() {
        
            return "Emp{" +
                    "teacherno='" + teacherno + '\'' +
                    ", tname='" + tname + '\'' +
                    ", major='" + major + '\'' +
                    ", prof='" + prof + '\'' +
                    ", department='" + department + '\'' +
                    '}';
        }
    }

//这才是主场
    public class TestSelect {
        
        public static void main(String[] args) throws SQLException {
        
            List<Emp> list = selectAll ();
            for (Emp emp:list){
        
                System.out.println (emp.getTeacherno ()+"\t"+emp.getTname ()+
                        "\t"+emp.getMajor ()+"\t"+emp.getProf ()+"\t"+emp.getDepartment ());
            }
        }
        //后台处理
        public static List<Emp> selectAll() throws SQLException {
        
            String driver = "com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user="root";
            String password="tcx119";
            Connection conn=null;
            Statement stmt = null;
            ResultSet rs=null;
            Emp emp = null;
            List<Emp> list =new ArrayList<Emp>();
            try{
        
                Class.forName (driver);
                conn = DriverManager.getConnection (url,user,password);
                stmt= conn.createStatement ();
                String sql = " select * from teacher";
                rs= stmt.executeQuery (sql);
                while(rs.next ()){
        
                    String teacherno = rs.getString ("teacherno");
                    String tname = rs.getString ("tname");
                    String major = rs.getString ("major");
                    String prof = rs.getString ("prof");
                    String department = rs.getString ("department");
                    emp=new Emp (teacherno,tname,major,prof,department);
                    list.add (emp);
                }
            } catch (ClassNotFoundException | SQLException e) {
        
                e.printStackTrace ();
            } finally {
        
                assert rs != null;
                rs.close ();
                stmt.close ();
                conn.close ();
            }
            return list;
        }
    }

### 模拟登录 ###

public class Login {
        
        public static void main(String[] args) throws SQLException, ClassNotFoundException {
        
            //前台
            Scanner sc = new Scanner (System.in);
            System.out.println ("请输入用户名");
            String userId = sc.next ();
            System.out.println ("请输入密码");
            String inputPassword = sc.next ();
            User user = testLogin (userId,inputPassword);
            if (user==null){
        
                System.out.println ("登录失败,请检查用户名或密码是否正确");
            }else{
        
                System.out.println ("登录成功： 姓名： "+user.getRealName ());
            }
    
        }
    //    后台
        public static User testLogin(String userId,String inputPassword) throws ClassNotFoundException, SQLException {
        
            String driver = "com.mysql.cj.jdbc.Driver";
            String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user="root";
            String pasaword="tcx119";
            Class.forName (driver);
            User user2=null;
            Connection conn = DriverManager.getConnection (url,user,pasaword);
            String sql = "select * from t_user where userid = ? and password = ? ";
    
    //        Statement stmt = conn.createStatement ();
            PreparedStatement pstmt = conn.prepareStatement (sql);
            pstmt.setString (1,userId);
            pstmt.setString (2,inputPassword);
            ResultSet rs =  pstmt.executeQuery ();
            if(rs.next ()){
        
                String realName=rs.getString ("realname");
                double money = rs.getDouble ("money");
                user2 = new User (userId,realName,inputPassword,money);
                return user2;
            }
            return user2;
        }
    }

### 事务 ###

public class TestTransation {
        
        public static void main(String[] args)  {
        
            Connection conn=null;
            try{
        
                String driver = "com.mysql.cj.jdbc.Driver";
                String url="jdbc:mysql://127.0.0.1:3306/shiyan?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
                String user="root";
                String pasaword="tcx119";
                Class.forName (driver);
                conn = DriverManager.getConnection (url,user,pasaword);
                Statement stmt = conn.createStatement ();
                conn.setAutoCommit (false);
                stmt.executeUpdate ("update t_user set money = money-2000 where userid = 'lisi' ");
                stmt.executeUpdate ("update t_user set money = money+2000 where userid = 'zhangsan' ");
                conn.commit ();
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace ();
            } catch (SQLException e) {
        
                //手动的回滚事务,回到所有DML操作执行之前的状态
                try {
        
                    assert conn != null;
                    conn.rollback();
                } catch (SQLException e1) {
        
                    e1.printStackTrace();
                }
    
                e.printStackTrace ();
            }
            
        }
    }

【都看到这了，点点赞点点关注呗，爱你们】??

![抽象工厂  引导关注][876f6971acd31bcf2b59747b7497f9d2.png]

**结语**

`谢谢你的阅读`，由于作者水平有限，难免有不足之处，若读者发现问题，还请批评，在留言区留言或者私信告知，我一定会尽快修改的。若各位大佬有什么好的解法，或者有意义的解法都可以在评论区展示额，万分谢谢。  
写作不易，望各位老板点点赞，加个关注!???

**作者：****后端小知识**

**CSDN**个人主页：[后端小知识][Link 1]

?**GZH**：`后端小知识`

?欢迎关注?点赞?收藏⭐️留言?

[Link 1]: https://blog.csdn.net/m0_46833224
[image-20231006234204124]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/dcb7fa71b3a541d5942720cc47b18b2d.png
[876f6971acd31bcf2b59747b7497f9d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/529f462593be40549d8047980ba6a47d.png