java Jdbc编程入门

JDBC是什么？

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。


示例代码如下：

DBUtil.java

package com.tingcream.springJdbc.db;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.tingcream.springJdbc.model.User;
 
public class DBUtil {
     
    private static  String dbUrl="jdbc:mysql://localhost:3306/myDB?useUnicode=true&characterEncoding=UTF8&useSSL=false";
    private static String dbUsername="root";//用户名
    private static String dbPassword="123456";//密码
    private static  String dbDriverClassName="com.mysql.jdbc.Driver";//驱动名称
     
     
    //获取数据库连接
    public static Connection  getConn() throws SQLException, ClassNotFoundException {
        Class.forName(dbDriverClassName);
        Connection conn=DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
        return conn ;
    }
    //关闭数据库连接
    public static void closeConn(Connection conn) throws SQLException{
        if(conn!=null){
            conn.close();
        }
    }
     
      
     public static void main(String[] args)  throws Exception{
         //testConn();//测试连接ok
          
//        User user=new User();
//       user.setName("李四");
//       user.setAge(22);
//       user.setCity("南京");
//       user.setDescribe("adfasdfasdfsdf");
//       user.setSex(2);
//         insertUser(user);
          
         //根据id 查询
//       User user= findById(1);
//       System.out.println(user);// ok
          
         //查询所有
//      List<User> list = findAllUser();
//      System.out.println(list);//ok
          
         //统计数量
         int totalCount= getUserCount();
         System.out.println("totalCount:"+totalCount);
    }
     
      
      /**
       * 测试连接 ok
       * @throws Exception
       */
     private  static void  testConn()throws Exception{
          Connection conn=null;
          try {
            conn=getConn();
            System.out.println("获取数据库连接成功");
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("获取数据库连接失败");
        }finally{
            closeConn(conn);
        }
      }
      
      /**
       * 根据id查询用户
       * @param id
       * @return
       * @throws Exception
       */
     private  static User  findById(Integer id )throws Exception{
          Connection conn=null;
          PreparedStatement ps=null;
          try {
              conn=getConn();
              String sql =" SELECT  * FROM t_user  WHERE id=?";
             ps  =  conn.prepareStatement(sql);
              ps.setInt(1, id);
              ResultSet rs =  ps.executeQuery();
              
               if(rs.next()){//如果有数据
                   User user =new User();
                   user.setId(rs.getInt("id"));
                   user.setAge(rs.getInt("age"));
                   user.setCity(rs.getString("city"));
                   user.setDescribe(rs.getString("describe"));
                   user.setName(rs.getString("name"));
                   user.setSex(rs.getInt("sex"));
                   return user;
               }else{
                   return null ;
               }
              
        }catch (Exception e) {
             e.printStackTrace();
        }finally{
            try {
                closeConn(conn);
                if(ps!=null){
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
         return  null;
      }
       
       
      /**
       * 查询所有用户
       * @return
       * @throws Exception
       */
    private   static  List<User> findAllUser()throws Exception {
         Connection conn=null;
          PreparedStatement ps=null;
          try {
             conn= getConn();
             String sql ="select  * from t_user";
            ps=conn.prepareStatement(sql);
             
            ResultSet rs =  ps.executeQuery();
             List<User>  list=new ArrayList<User>();
            while(rs.next()){
                   User user =new User();
                   user.setId(rs.getInt("id"));
                   user.setAge(rs.getInt("age"));
                   user.setCity(rs.getString("city"));
                   user.setDescribe(rs.getString("describe"));
                   user.setName(rs.getString("name"));
                   user.setSex(rs.getInt("sex"));
                    
                   list.add(user);
            }
            return list ;
        } catch (Exception e) {
             e.printStackTrace();
        }finally{
            try {
                if(ps!=null){
                    ps.close();
                }
                closeConn(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
           return null ;
       }
        
        
       /**
        * 计算用户总数 count(*)
        * @return
        * @throws Exception
        */
     private  static int  getUserCount()throws Exception {
           Connection conn=null;
             PreparedStatement ps=null;
             try {
                 conn= getConn();
                 String sql ="select count(*) from t_user";
                 ps=conn.prepareStatement(sql);
                ResultSet rs= ps.executeQuery();
                int totalCount=0;
                if(rs.next()){
                    totalCount=rs.getInt(1);
                }
                return totalCount;
            } catch (Exception e) {
                 e.printStackTrace();
            }finally{
                try {
                    if(ps!=null){
                        ps.close();
                    }
                    closeConn(conn);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            return 0;
              
       }
     
        /**
         * insert 用户
         * @param user
         * @return 受影响的行数
         * @throws Exception
         */
    private   static int  insertUser(User  user) throws Exception {
         Connection conn=null;
         PreparedStatement ps=null;
         try {
             conn= getConn();
              String sql ="INSERT INTO t_user(`name`,sex,age,city,`describe`) VALUES(?,?,?,?,?)";
                 ps = conn.prepareStatement(sql);
                 ps.setString(1, user.getName());
                ps.setInt(2, user.getSex());
                ps.setInt(3, user.getAge());
                ps.setString(4, user.getCity());
                ps.setString(5, user.getDescribe());
                 
               return  ps.executeUpdate();
              
        } catch (Exception e) {
             e.printStackTrace();
        }finally{
            try {
                if(ps!=null){
                    ps.close();
                }
                closeConn(conn);
                 
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
          
     }
      
      
} 

User.java

package com.tingcream.springJdbc.model;
 
import java.io.Serializable;
 
public class User implements Serializable{
 
    private static final long serialVersionUID = 1L;
    private Integer id;//id主键自增
    private String  name;//姓名
    private Integer sex;//性别 1男 2女
    private Integer age;//年龄
    private String city;//城市
    private String describe;//描述
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getSex() {
        return sex;
    }
    public void setSex(Integer sex) {
        this.sex = sex;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getCity() {
        return city;
    }
    public void setCity(String city) {
        this.city = city;
    }
    public String getDescribe() {
        return describe;
    }
    public void setDescribe(String describe) {
        this.describe = describe;
    }
     
}

pom.xml 

<!-- mysql jdbc 驱动 -->
 <dependency>
      <groupid>mysql</groupid>
      <artifactid>mysql-connector-java</artifactid>
      <version>5.1.40</version>
  </dependency>

ok !!