Java学习 JDBC

一、JDBC方式的基本流程

        JDBC (Java DataBase Connection) 是通过JAVA访问数据库。这里访问mysql数据库。

        1. 为项目导入mysql-jdbc的jar包。

        2. 初始化驱动包

        3. 建立与数据库连接

        4. 创建statement

        5. 执行SQL语句

        6. 关闭连接

public static void main(String[] args) throws SQLException {
		Connection c = null;
		Statement s = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("数据库驱动加载成功");
			c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/tmall?characterEncoding=UTF-8",
	                "root", "!admin");
			System.out.println("连接成功，获取连接对象： " + c);
			s = c.createStatement();
			System.out.println("获取 Statement对象： " + s);
 
			String sql = "select count(*) from Category";
 
			ResultSet rs = s.executeQuery(sql);
			int total = 0;
			while (rs.next()) {
				total = rs.getInt(1);
			}
			System.out.println("表Hero中总共有:" + total+" 条数据");
			System.out.println("执行语句成功");	 
		} catch(ClassNotFoundException e) {
			e.printStackTrace();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			// 数据库的连接时有限资源，相关操作结束后，养成关闭数据库的好习惯
            // 先关闭Statement
            if (s != null)
                try {
                    s.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            // 后关闭Connection
            if (c != null)
                try {
                    c.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
		}
}

如果觉得上面的关闭方式麻烦，可以使用try-with-resource的方式自动关闭连接。

public static void main(String[] args) {  
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
  
        try (
            Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
                "root", "admin");
            Statement s = c.createStatement();             
        )
        {
            String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
            s.execute(sql);
              
        } catch (SQLException e) {
            e.printStackTrace();
        }
}

二、增删改查

        JDBC中增删改的操作只是执行语句的差别，查询需要返回数据并处理，与增删改不一样。

public static void main(String[] args) throws SQLException {
		//初始化驱动
        try {
            //驱动类com.mysql.jdbc.Driver
            //就在 mysql-connector-java-5.0.8-bin.jar中
            //如果忘记了第一个步骤的导包，就会抛出ClassNotFoundException
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("数据库驱动加载成功 ！");
        } catch(ClassNotFoundException e) {
        	e.printStackTrace();
        }
        try(Connection c = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
                "root", "admin");
        	Statement s = c.createStatement();)
        {
        	String sql = "select * from hero";
        	// 执行查询语句，并把结果集返回给ResultSet
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");// 可以使用字段名
                String name = rs.getString(2);// 也可以使用字段的顺序
                float hp = rs.getFloat("hp");
                int damage = rs.getInt(4);
                System.out.printf("%d\t%s\t%f\t%d%n", id, name, hp, damage);
            }
            s.execute(sql);  
            System.out.println("执行插入语句成功");
        } catch (SQLException e) {
            e.printStackTrace();
        } 
	}
}