MySQL数据库及Java操作MySQL数据库
来源:互联网 发布:淘宝联盟查找订单号 编辑:程序博客网 时间:2024/05/21 09:11
创建表: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指向要访问的数据库名scutcsString 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.javaD:\testjdbc>java JDBCTestSucceeded 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(); } } } }}
0 0
- MySQL数据库及Java操作MySQL数据库
- Java 第二次作业:MySQL数据库及Java操作MySQL数据库
- 第二个作业:MySQL数据库及Java操作MySQL数据库
- 第二次作业:MySQL数据库及Java操作MySQL数据库
- 第二次作业:MySQL数据库及Java操作MySQL数据库
- 第二次作业:MySQL数据库及Java操作MySQL数据库
- java连接mysql实例及数据库操作
- java操作数据库MYSQL
- java操作mysql数据库
- java操作mysql数据库
- JAVA操作mysql数据库
- java 操作mysql数据库
- java操作mysql数据库
- MYSQL数据库基础知识及操作
- mysql连接及数据库操作
- 《Java程序设计》第二次作业:MySQL数据库及Java操作MySQL数据库
- 《Java程序设计》第二次作业:MySQL数据库及Java操作MySQL数据库
- 《Java程序设计》第二次作业:MySQL数据库及Java操作MySQL数据库
- nodejs多线程,真正的非阻塞
- uva 10935
- 图解CSS3 Flexbox属性
- 支持向量机(SVM)笔记
- leetcode 第94题 Binary Tree Inorder Traversal
- MySQL数据库及Java操作MySQL数据库
- pipe管道
- 病毒木马常用API
- flexbox大全
- HDU 3072 连通分量+最小树形图
- 病毒分析基础
- Spark(八) -- 使用Intellij Idea搭建Spark开发环境
- Web系统大规模并发-电商秒杀与抢购
- 第十二周阅读项目二