MySQL数据库及Java操作MySQL数据库

转载 于 2015-05-24 20:06:47 发布 · 555 阅读 · 1

本文详细介绍了如何使用Java连接MySQL数据库，并演示了如何在Java中操作数据库表，包括查询、添加、查找、更新和删除数据。同时，展示了如何通过Java实现与数据库交互的简单实体类，并使用DAO模式进行数据操作。 
创建表：
CREATE TABLE STUDENT  
(  
SNO CHAR(7) NOT NULL,  
SNAME VARCHAR(8) NOT NULL,  
SEX CHAR(2) NOT NULL,  
BDATE DATE NOT NULL,  
HEIGHT DEC(5,2) DEFAULT 000.00,  
PRIMARY KEY(SNO)  
);  
然后插入数据，可以用SQL语句insert into <表名> values (value1, value2, ...);
也可以用SQLyog来操作
好了，创建好了。
下面，我们来编写.java文件来演示一下如何访问Java连接MySQL数据库。
import java.sql.*;   
public class JDBCTest {   
public static void main(String[] args){  
驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名scutcs
String url = "jdbc:mysql://127.0.0.1:3306/scutcs";
// MySQL配置时的用户名
String user = "root";
// Java连接MySQL配置时的密码
String password = "root";
try {
// 加载驱动程序
Class.forName(driver);
// 连续数据库
Connection conn = DriverManager.getConnection(url, user, password);
if(!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
// statement用来执行SQL语句
Statement statement = conn.createStatement();
// 要执行的SQL语句
String sql = "select * from student";
结果集
ResultSet rs = statement.executeQuery(sql);  
System.out.println("-----------------");  
System.out.println("执行结果如下所示:");  
System.out.println("-----------------");  
System.out.println(" 学号" + "\t" + " 姓名");  
System.out.println("-----------------");  
String name = null;  
while(rs.next()) {  
选择sname这列数据
name = rs.getString("sname");
// 首先使用ISO-8859-1字符集将name解码为字节序列并将结果存储新的字节数组中。
// 然后使用GB2312字符集解码指定的字节数组
name = new String(name.getBytes("ISO-8859-1"),"GB2312");// 输出结果
System.out.println(rs.getString("sno") + "\t" + name);  
}  
rs.close();  
conn.close();   
} catch(ClassNotFoundException e) {   
System.out.println("Sorry,can`t find the Driver!");   
e.printStackTrace();   
} catch(SQLException e) {   
e.printStackTrace();   
} catch(Exception e) {   
e.printStackTrace();   
}   
}   
}  
接下来我们运行一下看下效果：
D:\testjdbc>javac JDBCTest.java
D:\testjdbc>java JDBCTest
Succeeded connecting to the Database!
-----------------------
执行结果如下所示:
-----------------------
学号 姓名
-----------------------
0104421 周远行
0208123 王义平
0209120 王大力
0309119 李 维
0309203 欧阳美林

//java操作MySQL数据库
package com.syxy.dao;
import com.syxy.domain.User;
//操纵数据库的 dao接口
public interface UserDao {
       //加入一个用户
       public void addUser(User user);
       //得到一个用户
       public User getUser( int userId);
       //寻找一个用户
       public User findUser(String loginName);
       //更新用户数据
       public void update(User user);
       //删除一个用户
       public void delete(User user);
}
//接口的实现类
package com.syxy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.syxy.dao.UserDao;
import com.syxy.domain.User;
import com.syxy.utils.jdbcUtils;

public class UserDaoJdbcImpl implements UserDao {

     @Override
     public void addUser(User user) {
          // TODO Auto-generated method stub
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "insert into user(name, birthday, monery) values(?,?,?)";
               ps = conn.prepareStatement(sql);
               ps.setString(1, user.getName());
               ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
               ps.setFloat(3, user.getMoney());
               ps.executeUpdate();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
     }

     @Override
     public User getUser(int userId) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          User user = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "select id，name,money,birthday from user where id=?";
               ps = conn.prepareStatement(sql);
               ps.setInt(1, userId);
               rs = ps.executeQuery();
               while(rs.next()){
                    user = MappingUser(rs);
               }
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
          return user;
     }

     @Override
     public User findUser(String loginName) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          User user = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "select id,money,birthday from user where name=?";
               ps = conn.prepareStatement(sql);
               ps.setString(1, loginName);
               rs = ps.executeQuery();
               while(rs.next()){
                    user = MappingUser(rs);
               }
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
          return user;
     }

     private User MappingUser(ResultSet rs) throws SQLException {
          User user = new User();
          user.setId(rs.getInt("id"));
          user.setName(rs.getString("name"));
          user.setMoney(rs.getFloat("money"));
          user.setBirthday(rs.getDate("birthday"));
          return user;
     }

     @Override
     public void update(User user) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "update user set name=?, birthday=?, money=? where id=?";
               ps = conn.prepareStatement(sql);
               ps.setString(1, user.getName());
               ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
               ps.setFloat(3, user.getMoney());
               ps.setInt(4, user.getId());
               ps.executeUpdate();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
     }

     @Override
     public void delete(User user) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "delete from user where id=?";
               ps = conn.prepareStatement(sql);
               ps.setInt(1, user.getId());
               ps.executeUpdate();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
     }
}



//domain用户类
package com.syxy.domain;
import java.util.Date;
public class User {
       private int id ;
       private String name ;
       private Date birthday ;
       private float money ;
      
       public int getId() {
             return id ;
      }
       public void setId( int id) {
             this .id = id;
      }
       public String getName() {
             return name ;
      }
       public void setName(String name) {
             this .name = name;
      }
       public Date getBirthday() {
             return birthday ;
      }
       public void setBirthday(Date birthday) {
             this .birthday = birthday;
      }
       public float getMoney() {
             return money ;
      }
       public void setMoney( float money) {
             this .money = money;
      }  
}


//dao工厂类（方便复用）
package com.syxy.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Properties;
public class DaoFactory {
       private static UserDao userDao = null ;
       private static DaoFactory instance = new DaoFactory();
      
       private DaoFactory(){
             try {
                  Properties prop = new Properties();
                  InputStream inStream = new FileInputStream(
                               new File("src/daoconfig.properties" ));
                  prop.load(inStream);
                  String userDaoClass = prop.getProperty("userDaoClass" );
                   userDao = (UserDao) Class.forName(userDaoClass).newInstance();
            } catch (Exception e) {
                   // TODO: handle exception
            }
      }
      
       public static DaoFactory getInstance(){
             return instance ;
      }
      
       public UserDao getUserDao(){
             return userDao ;
      }
}


//操作数据库的工具类
package com.syxy.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class jdbcUtils {
     private static String url = "jdbc：mysql://localhost:3306/jdbc";
     private static String user = "root";
     private static String password = "lxtalx";
     
     private  jdbcUtils(){}
     static{
          try {
               Class.forName("com.mysql.jdbc.Driver");
          } catch (ClassNotFoundException e) {
               throw new ExceptionInInitializerError(e);
          }
     }
     
     public static Connection getConnection() throws SQLException{
          return DriverManager.getConnection(url, user, password);
     }
     
     public static void free(ResultSet rs, Statement st, Connection conn){
          try {
               if(rs!=null) 
                    rs.close();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               try {
                    if(st!=null)
                         st.close();
               } catch (Exception e2) {
                    e2.printStackTrace();
               }finally{
                    try {
                         conn.close();
                    } catch (Exception e3) {
                         e3.printStackTrace();
                    }
               }
          }
     }
}

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值