创新实训6 语句模块

week6

语句模块

搜集用到的语句的素材

英文例句，翻译，美式发音音频文件，

保存到SQlLite数据库中

插入数据

	public static void insert_sentense(int table,int id,String sentence,String trans,String pic,String audio,String word){
		try{
			String sql = "jdbc:sqlite://e:/test.db";
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection(sql);
			PreparedStatement stat= conn.prepareStatement("insert into sentence"+table+" (sentence_id,sentence,trans,pic,audiofile,word) values (?,?,?,?,?,?)");
			stat.setInt(1, id);
			
			stat.setString(2, sentence);
			
			stat.setString(3, trans);
			
			int mtable = table;
			//String strimg = "E:\\bird\\bird.jpg";
			//File img = new File(strimg);
			String npic = "E:/Creation/sentence/unit"+table+"/"+word+"/"+pic;
			byte[] imgbytes=IOUtils.toByteArray(new FileInputStream(npic));
			stat.setBytes(4, imgbytes);
			//stat.setCharacterStream(4, new FileReader(img), (int)img.length());
			
			//stat.setString(5, yb);
			
			//String straudio="E:\\创新实训\\word\\bird\\bird.mp3";
			//File aud = new File(straudio);
			String naudio = "E:/Creation/sentence/unit"+table+"/"+word+"/"+audio;
			byte[] audio_bytes=IOUtils.toByteArray(new FileInputStream(naudio));
			stat.setBytes(5, audio_bytes);
			//stat.setCharacterStream(6, new FileReader(aud),(int)aud.length());
			stat.setString(6, word);
			stat.executeUpdate();
	        stat.close();
	        conn.close();
	        System.out.println("success");
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
	}

更新语句

public static void update(int id,String imgstr){
		try{
			String sql = "jdbc:sqlite://e:/test.db";
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection(sql);
			PreparedStatement stat= conn.prepareStatement("update sentence set word = ? where sentence_id = ?");
			
		//	byte[] imgbytes=IOUtils.toByteArray(new FileInputStream(imgstr));
		//	stat.setBytes(1, imgbytes);
			stat.setString(1, imgstr);
			stat.setInt(2, id);
			
			stat.executeUpdate(); 
			System.out.println("success");
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println(e);
		}
		
	}

添加列

public static void addSenColume(){
		try{
			String sql = "jdbc:sqlite://e:/test.db";
			Class.forName("org.sqlite.JDBC");
			Connection conn = DriverManager.getConnection(sql);
			Statement stat=  conn.createStatement();
			stat.executeUpdate("alter table sentence add column word varchar(20); ");
			System.out.println("success");
		}catch (Exception e) {
			System.out.println(e);
		}
	}

对图片进行抠图并保存为PNG格式

语句界面的脚本

获得语句的数据，从数据库中根据相应的单元获得语句的相关数据并储存在临时数组中

 public void getSentenceData(int table) {
        sql = new SQLiteHelper(@"data source=E:\\test.db");
        if(table==1)
            reader1 = sql.ReadFullTable("sentence");
        else
            reader1 = sql.ReadFullTable("sentence"+table);
        index = 0;
        while (reader1.Read())
        {
            gloable_variable.sen_pic[index] = reader1["pic"] as byte[];


            //MemoryStream streamImage = new MemoryStream(reader1["pic"] as byte[]);
            //bitmaps[index] = new Bitmap((System.Drawing.Image)new Bitmap(streamImage));
            //images[index] = Bitmap2Image(bitmaps[index]);
            // GetBytesToPic(bytes[index]);
            //images[index] = BytesToImage(bytes[index]);
            gloable_variable.sentence[index] = reader1["sentence"].ToString();
            gloable_variable.sen_trans[index] = reader1["trans"].ToString();
            gloable_variable.sen_word[index] = reader1["word"].ToString();
           // gloable_variable.pgram[index] = reader1["pgram"].ToString();
            index++;
        }
    }

根据选择的单元 选择数据表

  NUM = gloable_variable.sen_num;
            switch (Unit)
            {
                case "第一单元":
                    unitnum = 1;
                    getSentenceData(1);
                    break;
                case "第二单元":
                    unitnum = 2;
                    getSentenceData(2);
                    break;
                case "第三单元":
                    unitnum = 3;
                    getSentenceData(3);
                    break;
                case "第四单元":
                    unitnum = 4;
                    getSentenceData(4);
                    break;
                default:
                    unitnum = 1;
                    getSentenceData(1);
                    break;

            }

将语句，翻译，图片，显示在界面中

 word.text = gloable_variable.sentence[index];
            gloable_variable.cursentence = gloable_variable.sentence[index];
            String belongWord = gloable_variable.sen_word[index];
            translation.text = gloable_variable.sen_trans[index];
            t2 = new Texture2D(100, 100);
            t2.LoadImage(gloable_variable.sen_pic[index]);
            Sprite sprite = Sprite.Create(t2, new Rect(0, 0, t2.width, t2.height), new Vector2(0.5f, 0.5f));
            pic.sprite = sprite;

            String sentenceMp3 = "E:/Creation/sentence/unit" + unitnum + "/" + belongWord + "/sen_" + belongWord + ".mp3";
            String sentenceWav = "E:/Creation/sentence/unit" + unitnum + "/" + belongWord + "/sen_" + belongWord + ".wav";
            StartCoroutine(LoadMusic(sentenceMp3, sentenceWav));
            gloable_variable.StudiedSenNum++;

添加语音识别的监听

 dictationRecognizer = new DictationRecognizer();
        dictationRecognizer.AutoSilenceTimeoutSeconds = (float)1.0; ;
        //订阅事件  
        dictationRecognizer.DictationHypothesis += DictationRecognizer_DictationHypothesis;
        dictationRecognizer.DictationResult += DictationRecognizer_DictationResult;
        dictationRecognizer.DictationComplete += DictationRecognizer_DictationComplete;
        dictationRecognizer.DictationError += DictationRecognizer_DictationError;
        dictationRecognizer.Start();

语句跳转

            Debug.Log("句子跳转");
            if (str == (gloable_variable.curword) || ((str == "过")))
            {
                first = false;
                answer = true;
                Debug.Log("kaixin");
                myanim.transform.GetComponent<Animation>().wrapMode = WrapMode.Once;
                myanim.transform.GetComponent<Animation>().Play("dzz_zhongxing");
                myanim.transform.GetComponent<Animation>()["dzz_zhongxing"].speed = 1;
                myanim.transform.GetComponent<Animation>().wrapMode = WrapMode.Loop;
                myanim.transform.GetComponent<Animation>().Play("dzz_kaixin");
                myanim.transform.GetComponent<Animation>()["dzz_kaixin"].speed = 1;

                //   OnDestroy();
                OnTypeButtonClick();
            }
            //System.Threading.Thread.Sleep(10000);
            //OnTypeButtonClick();