Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Read and Write JSON data in MySQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 7.75k
    Comment on it

    Store JSON in MYSQL:- Here below snippet will show you how we can store json data in mysql and read the json data from mysql and convert into a java object. Remember that the field where you can store the JSON data in mysql to set data type of that field is TEXT.

    pom.xml

    project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>com</groupId>
      <artifactId>StoreJson</artifactId>
      <version>0.0.1-SNAPSHOT</version>
    
      <dependencies>
    
          <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20090211</version>
        </dependency>
          <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
          <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.13</version>
        </dependency>
    
      </dependencies>
    
    </project>
    

    EmpModel.java

    public class EmpModel {
    
        private int empId;
        private String name;
        private String notes;
    
        public int getEmpId() {
            return empId;
        }
        public void setEmpId(int empId) {
            this.empId = empId;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public String getNotes() {
            return notes;
        }
        public void setNotes(String notes) {
            this.notes = notes;
        }
    
    
    
    }
    

    WriteJSONData.java

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.Statement;
    import org.codehaus.jackson.map.ObjectMapper;
    
    
    public class WriteJSONData {
    
        /**
         * @param args
         */
        public static void main(String[] args) {
            // TODO Auto-generated method stub
                try{
                    ObjectMapper objMap=new ObjectMapper();
    
                    EmpModel empModel=new EmpModel();
                    empModel.setEmpId(101);
                    empModel.setName("Manish");
                    empModel.setNotes("Hello manish how are you");
                    Connection con=null;
                    Class.forName("com.mysql.jdbc.Driver");
                    con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahi","root","");
                    PreparedStatement pstmt=con.prepareStatement("INSERT INTO emp(empId,name,notes) VALUES(?,?,?)");
                    pstmt.setInt(1,101 );
                    pstmt.setString(2, "Manish");
                    pstmt.setObject(3, objMap.defaultPrettyPrintingWriter().writeValueAsString(empModel));
    
                    pstmt.execute();
    
                }
                catch(Exception e){e.printStackTrace();}
    
    
                }
    
    
    
        }
    

    ReadJSONData.java

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    
    import org.codehaus.jackson.map.ObjectMapper;
    
    
    public class ReadJSONData {
    
        /**
         * @param args
         */
        public static void main(String[] args)throws Exception {
            ObjectMapper objMap=new ObjectMapper();
            Connection con=null;
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mahi","root","");
            PreparedStatement pstmt=con.prepareStatement("SELECT * FROM emp");
            ResultSet rs=pstmt.executeQuery();
    
            while(rs.next())
            {
    
                EmpModel empModel=(EmpModel)objMap.readValue(rs.getString(3), EmpModel.class);
                System.out.println(empModel.getEmpId());
                System.out.println(empModel.getName());
                System.out.println(empModel.getNotes());
    
            }
    
        }
    
    }
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: