最简便的java-jdbc-mysql

爱被打了一巴掌 2022-05-28 03:51 141阅读 0赞

0x001 环境介绍

编写环境：win10

编写工具:eclipse neon

jdk:1.8

数据库：mysql

数据表创建语句:

CREATE TABLE `score` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `teacher_id` varchar(10) NOT NULL,
       `student_id` varchar(10) NOT NULL,
       `subject_id` varchar(10) NOT NULL,
       `score` int(11) DEFAULT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

0x002 这是案例代码

//这是help工具类

package com.utills.jdbc;
     
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import com.mysql.jdbc.MySQLConnection;
    import com.mysql.jdbc.StringUtils;
    
    /*
     * jdbc
     * mybatis
     * */
    public class MysqlHelper {	
    	
    	//先放这里，要做成注解配置文件的模式
    	//不然这里有很多冗余代码 和 代码固化问题
    	//没有写成实体类的模式
    	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    	//reacherdb 数据库名字
    	static final String DB_URL = "jdbc:mysql://localhost:3306/reacherdb";
        // 数据库的用户名与密码，需要根据自己的设置
        static final String USER = "root";
        static final String PASS = "admin";
    	
        private Connection conn;
        private Statement  smt;
     
        
        //初始化
    	public MysqlHelper() throws SQLException {
    		// TODO Auto-generated constructor stub
    		conn = DriverManager.getConnection(DB_URL,USER,PASS);
    		smt = conn.createStatement();
    		
    	}
    	
    	//写死的查询
    	/* 
    	public Object GetSelect(String sql) throws SQLException
    	{
    		if(StringUtils.isNullOrEmpty(sql))
    		{
    			return null;
    		}
     
    		List<String> list = new ArrayList<String>();
    		ResultSet rs = smt.executeQuery(sql);
            while (rs.next()) {
                  
                 list.add(rs.getString("id"));
                 list.add(rs.getString("teacher_id"));
                 list.add(rs.getString("student_id"));
                 list.add(rs.getString("subject_id"));
                 list.add(rs.getString("score"));
             }
                  
    		return list;     
    	}*/
    	
    	
    	//可以写一个实体类进行获取
    	//返回类型可以是list 或 map
    	public List GetSelect1(String sql) throws SQLException
    	{
    		List list = new ArrayList();
    		ResultSet rs = smt.executeQuery(sql);
    		
            ResultSetMetaData md = rs.getMetaData();
            int columnCount = md.getColumnCount();
    
            while (rs.next()) {
    
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                	
                    rowData.put(md.getColumnName(i), rs.getObject(i));
                }
    
                list.add(rowData); 
            }
    
            return list;    //map 数据格式：{subject_id=2, score=75, teacher_id=2, student_id=2, id=1}
    	}
    	
    	
    	/*
    	 * 执行update delete insert
    	 * 这个只是一种写法
    	 * */
    	public int SqlExecute(String sql) throws SQLException
    	{
    		int retStatus = -1;
    		
    		if(StringUtils.isNullOrEmpty(sql))
    		{
    			return -1;
    		}
    	
    		//返回执行条目数
    		retStatus = smt.executeUpdate(sql);
    		
    		return retStatus;
    	}
    	
    	//分页没做
    	
    	//做回收功能
    	public void finalize(){
    	     
    	}
    	
    }

//测试主函数

package com.http.test;
    
    
     
    import java.sql.SQLException;
    import java.util.*;
    
    import com.mysql.jdbc.SocketMetadata.Helper;
    import com.utills.jdbc.*;;
     
    public class HttpFunc {
    	
        public static void main(String[] args) throws SQLException {  
        	 
        	MysqlHelper mysql = new MysqlHelper();
        	
        	String sql = "select * from reacherdb.score";
        	//List<String> list = (List<String>) mysql.GetSelect(sql);
        	List list = mysql.GetSelect1(sql);
        	
        	for(int i = 0; i < list.size(); i++)
        	{
        		System.out.println(list.get(i));
        	}
        	
        	//{subject_id=2, score=75, teacher_id=2, student_id=2, id=1}
        	String insertSql = "delete from reacherdb.score where teacher_id = 5";
        	//约定好返回值，这样才能更好的确认sql执行情况
        	int status = mysql.SqlExecute(insertSql);
        	if(-1 == status)
        	{
        		System.out.println("语句失败!");
        	}else{
        		System.out.println(status);
        	}
        }  
      
    }