1.建立数据库、表
如图:表结构,数据库名为test
2.建立java项目
建立一个文件夹,找一个mysql驱动jar包丟进去,buildpath
监理User实体类,重写一下toString方便查看。
public class User { private int id; private String userName; private String passWord; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassWord() { return passWord; } public void setPassWord(String passWord) { this.passWord = passWord; } @Override public String toString() { return "User [id=" + id + ", userName=" + userName + ", passWord=" + passWord + "]"; } }
dao
public interface IUserDao { public int saveUser(User user); public void deleteUser(User user); public int updateUser(User user); public User selectUserById(int id); }
public class UserDaoImpl implements IUserDao { @Override public int saveUser(User user) { int result=0; try { Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); //获得数据库连接 String sql="insert into user (userName,passWord)value(?,?)"; PreparedStatement statement = conn.prepareStatement(sql); //预执行,置换参数,从1开始的 statement.setString(1, user.getUserName()); statement.setString(2, user.getPassWord()); result = statement.executeUpdate(); //修改了数据的使用该方法,没有修改数据的使用execute() } catch (Exception e) { e.printStackTrace(); } return result; } @Override public void deleteUser(User user) { int result=0; try { Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); //获得数据库连接 String sql="delete from user where userName=? and passWord=?"; PreparedStatement statement = conn.prepareStatement(sql); //预执行,置换参数,从1开始的 statement.setString(1, user.getUserName()); statement.setString(2, user.getPassWord()); result = statement.executeUpdate(); //修改了数据的使用该方法,没有修改数据的使用execute() System.out.println(result); } catch (Exception e) { e.printStackTrace(); } } @Override public int updateUser(User user) { return 0; } @Override public User selectUserById(int id) { return null; } public static void main(String[] args) { UserDaoImpl userDaoImpl=new UserDaoImpl(); User user=new User(); user.setPassWord("123456"); user.setUserName("帅哥"); userDaoImpl.deleteUser(user); } }
当然,实现着实际不能这样,起码要搞一个基本的工具类,写静态代码块的加载驱动一次、获取连接、关闭连接等操作方法。
2.研究一下时间日期在sql和java中的关系。
数据库中对应的日期类型格式如图,date是没有时分秒的,timestamp的总体时间区间是没有datetime大的(上限)。在写入数据库的时候可以按照都转换为sql的timestamp类型写入数据库,在数据库会自动变成它们定义的类型。
@Override public int saveUser(User user) { int result=0; try { Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); //获得数据库连接 String sql="insert into user (userName,passWord,date,datetime,timestamp)value(?,?,?,?,?)"; PreparedStatement statement = conn.prepareStatement(sql); //预执行,置换参数,从1开始的 statement.setString(1, user.getUserName()); statement.setString(2, user.getPassWord()); statement.setTimestamp(3, new Timestamp(user.getDate().getTime())); //需要转换一下才行 statement.setTimestamp(4, new Timestamp(user.getDatetime().getTime())); statement.setTimestamp(5, user.getTimestamp()); result = statement.executeUpdate(); //修改了数据的使用该方法,没有修改数据的使用execute() } catch (Exception e) { e.printStackTrace(); } return result; }
取出来:要转换一下,否则会丢失了精度。还有后面还有.0,贼麻烦。建议统统使用string记录就好了
public User selectUserById(int id) { User user=null; try { Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); String sql="select * from user where id = 5"; Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(sql); while(resultSet.next()){ user=new User(); user.setId(resultSet.getInt(1)); user.setPassWord(resultSet.getString(2)); user.setUserName(resultSet.getString("userName")); user.setDate(resultSet.getDate("date")); // user.setDatetime(resultSet.getDate(5)); //这里数据库使用的类型为datetime=YYYY-MM-DD HH:MM:SS,取出来就没了时分秒了 user.setDatetime(resultSet.getTimestamp(5)); //使用这个timestamp就可以获得全。 user.setTimestamp(resultSet.getTimestamp("timestamp")); } } catch (Exception e) { e.printStackTrace(); } return user; }
3.数据库写入图片。正常没什么卵用的东西,还不如直接存在服务器上,搞在数据库压力很大的。
public class TestBlob { public static void main(String[] args) { //add(); read(); } public static void read(){ Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = JDBCUtil.getConnection(); String sql = "select * from user where id=?"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1, 8); resultSet = preparedStatement.executeQuery(); if(resultSet.next()){ //InputStream inputStream = resultSet.getBinaryStream("pic"); Blob blob = resultSet.getBlob("pic"); InputStream inputStream = blob.getBinaryStream(); OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(new File("d:/bak4.jpg"))); byte[] buffer = new byte[1024]; for(int i=0;(i=inputStream.read(buffer))>0;){ outputStream.write(buffer, 0, i); } inputStream.close(); outputStream.close(); } } catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }finally{ JDBCUtil.free(resultSet, preparedStatement, connection); } } /** * */ public static void add() { Connection connection = null; PreparedStatement preparedStatement = null; try { // 在指定的文件上插入读取管道 File file = new File("d:/4.jpg"); InputStream inputStream = new BufferedInputStream(new FileInputStream(file)); connection = JDBCUtil.getConnection(); String sql = "insert into user (username,password,pic) values (?,?,?)"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "root"); preparedStatement.setString(2, "root"); preparedStatement.setBinaryStream(3, inputStream, file.length()); preparedStatement.executeUpdate(); inputStream.close(); } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { JDBCUtil.free(null, preparedStatement, connection); } } }
4.写入大文本文件。这个有时候还有点用
public class TestText { public static void main(String[] args) { // add(); read(); } public static void read() { Connection con = null; PreparedStatement preparedStatement = null; ResultSet resultset = null; try { Writer writer = new BufferedWriter(new FileWriter( "c:/bak_db_kaoqin.txt")); con = JDBCUtil.getConnection(); String sql = "select * from user where id=?"; preparedStatement = con.prepareStatement(sql); preparedStatement.setInt(1, 12); resultset = preparedStatement.executeQuery(); if (resultset.next()) { char[] date = new char[1024]; int length = 0; Reader reader = resultset.getCharacterStream("info"); while ((length = reader.read(date)) != -1) { writer.write(date, 0, length); } reader.close(); } writer.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { JDBCUtil.free(resultset, preparedStatement, con); } } public static void add() { Connection con = null; PreparedStatement preparestatement = null; try { File file = new File("c:/db_kaoqin.sql"); Reader reader = new BufferedReader(new FileReader(file)); con = JDBCUtil.getConnection(); String sql = "insert into user (username,info) values(?,?)"; preparestatement = con.prepareStatement(sql); preparestatement.setString(1, "root"); preparestatement.setCharacterStream(2, reader, file.length()); preparestatement.executeUpdate(); try { reader.close(); } catch (IOException e) { e.printStackTrace(); } } catch (SQLException e) { e.printStackTrace(); } catch (FileNotFoundException e) { e.printStackTrace(); } finally { JDBCUtil.free(null, preparestatement, con); } } }
好了,如有不足或者错误请各位指出,虚心请教!
5.批量sql操作
@Override public int allExcuse() throws Exception { String sql1="insert into user (userName) value(?)"; Class.forName("com.mysql.jdbc.Driver"); //加载驱动,只加载一次就行 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); PreparedStatement statement = connection.prepareStatement(sql1); statement.setString(1, "张三"); statement.addBatch(); statement.setString(1, "李四"); statement.addBatch(); statement.setString(1, "王五"); statement.addBatch(); statement.setString(1, "甲"); statement.addBatch(); statement.setString(1, "乙"); statement.addBatch(); statement.setString(1, "丙"); statement.addBatch(); int[] batch = statement.executeBatch(); System.out.println(batch); return 0; }