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>